Returns values from the first row of a window.
FIRST_VALUE( 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 argument.
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.
SQL/2003 Vendor extension.
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. Send feedback about this page using email. |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |