LAST_VALUE

The LAST_VALUE function is an analytic function that returns the last value in an ordered set of values.

SQL syntax

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

Parameters

LAST_VALUE has the parameters:

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

Description

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

Example

Use the LAST_VALUE function to return for each row the hire date of the employee with the highest salary.

Command> SELECT last_name, salary, hire_date,
           LAST_VALUE (hire_date) OVER (ORDER BY salary
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lv
         FROM
           (SELECT * FROM employees WHERE department_id = 90 ORDER BY hire_date)
         ORDER BY last_name, salary, hire_date, lv;
< De Haan, 17000, 1993-01-13 00:00:00, 1987-06-17 00:00:00 >
< King, 24000, 1987-06-17 00:00:00, 1987-06-17 00:00:00 >
< Kochhar, 17000, 1989-09-21 00:00:00, 1987-06-17 00:00:00 >
3 rows found.