Returns values from the first row of a window.
FIRST_VALUE( expression [ IGNORE NULLS ] )
OVER ( window-spec )
window-spec : see the Remarks section below
expression The expression to evaluate. For example, a column name.
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 IGNORE NULLs is not specified, 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;
In the result set below, since employee 1658 is the first row for department 500, you know that they are the most recent hire in that department; therefore, their percentage is set to 100%. Then, percentages for the remaining employees in department 500 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.