Returns values from the first row of a window.
FIRST_VALUE( [ ALL ] expression [ { RESPECT | IGNORE } NULLS ] ) OVER ( window-spec )
window-spec : see the Remarks section below
expression The expression to evaluate. For example, a column name.
Data type of the values from the first row of a window.
The FIRST_VALUE function allows you to select the first value (according to some ordering) in a table, without having to use a self-join. This is valuable when you want to use the first value as the baseline in calculations.
The FIRST_VALUE function takes the first record from the window. Then, the expression is computed against the first record and results are returned.
If IGNORE NULLS is specified, the first non-NULL value of expression is returned. If RESPECT NULLS is specified (the default), the first value is returned whether or not it is NULL.
The FIRST_VALUE function is different from most other aggregate functions in that it can only be used with a window specification.
Elements of window-spec can be specified either in the function syntax (inline), or in conjunction with a WINDOW clause in the SELECT statement. See the window-spec definition provided in WINDOW clause.
For more information about using window functions in SELECT statements, including working examples, see Window functions.
For more information about specifying a window specification in an OVER clause, see Window definition: inlining using the OVER clause and WINDOW clause.
SQL/2008 Vendor extension.
SQL Anywhere supports SQL/2008 language feature F441, "Extended set function support", which permits operands of window functions to be arbitrary expressions that are not column references.
SQL Anywhere does not support optional SQL/2008 feature F442, "Mixed column references in set functions". SQL Anywhere does not permit the arguments of an aggregate function to include both a column reference from the query block containing the FIRST_VALUE function, combined with an outer reference. For an example, see the AVG function [Aggregate]
The following example returns the relationship, as a percentage, between each employee's salary and that of the most recently hired employee in the same department:
SELECT DepartmentID, EmployeeID, 100 * Salary / ( FIRST_VALUE( Salary ) OVER ( PARTITION BY DepartmentID ORDER BY StartDate DESC ) ) AS percentage FROM Employees; |
DepartmentID | EmployeeID | percentage |
---|---|---|
500 | 1658 | 100 |
500 | 1615 | 110.4284624 |
500 | 1570 | 138.8427097 |
500 | 1013 | 109.5851905 |
500 | 921 | 167.4497049 |
500 | 868 | 113.2393688 |
500 | 750 | 137.7344095 |
500 | 703 | 222.8679276 |
500 | 191 | 119.6642975 |
400 | 1751 | 100 |
400 | 1740 | 99.705647 |
400 | 1684 | 130.969936 |
400 | 1643 | 83.9734797 |
400 | 1607 | 175.1828989 |
400 | 1576 | 197.0164609 |
... | ... | ... |
Employee 1658 is the first row for department 500, indicating that they are the most recent hire in that department and their percentage is 100%. Percentages for the remaining department 500 employees are calculated relative to that of employee 1658. For example, employee 1570 earns approximately 139% of what employee 1658 earns.
If another employee in the same department makes the same salary as the most recent hire, they will have a percentage of 100 as well.
Discuss this page in DocCommentXchange.
|
Copyright © 2010, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.0 |