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

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

• Window aggregate functions
• LAST_VALUE function [Aggregate]

Standards and compatibility

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.