FIRST_VALUE function [Aggregate]

Returns values from the first row of a window.

Syntax
FIRST_VALUE( expression [ IGNORE NULLS ] )
OVER ( window-spec )
window-spec : see the Remarks section below
Parameters
  • expression   The expression to evaluate. For example, a column name.

Remarks

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.

See also
Standards and compatibility
  • SQL/2003   Vendor extension.

Example

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.

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
... ... ...