FIRST_VALUE

The FIRST_VALUE function is an analytic function that returns the first value in an ordered set of values.

SQL syntax

FIRST_VALUE (Expression [IGNORE NULLS]) OVER (AnalyticClause)

Parameters

FIRST_VALUE has the parameters:

Parameter	Description
`Expression`	For information on supported `Expressions`, see "Analytic Functions".
`IGNORE` `NULLS`	Specify `IGNORE` `NULLS` if you want `FIRST_VALUE` to return the first non-null value in the set or `NULL` if all values in the set are `NULL`. Clause is optional.
`OVER (AnalyticClause)`	For information on syntax, semantics, and restrictions, see "Analytic Functions".

Description

If the first value in the set is NULL, then FIRST_VALUE returns NULL unless you specify IGNORE NULLS. Specify IGNORE NULLS if you want the function to return the first non-null value in the set or NULL if all values in the set are NULL.

Example

Use the FIRST_VALUE function to select for each employee in department 90, the last name of the employee with the lowest salary.

Command> SELECT department_id, last_name, salary, FIRST_VALUE (last_name) OVER
          (ORDER BY salary ASC ROWS UNBOUNDED PRECEDING) AS lowest_sal
         FROM
          (SELECT * FROM employees WHERE department_id = 90 ORDER BY employee_id)
           ORDER BY department_id, last_name, salary, lowest_sal;
< 90, De Haan, 17000, Kochhar >
< 90, King, 24000, Kochhar >
< 90, Kochhar, 17000, Kochhar >
3 rows found.