FIRST_VALUE( [ ALL ] expression [ { RESPECT | IGNORE } NULLS ] )
OVER ( window-spec )

window-spec : see the Remarks section below

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. More information is provided in the window-spec definition of the WINDOW clause. See 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.

• Window aggregate functions
• LAST_VALUE 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;

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.