LAST_VALUE

Syntax

Description of the illustration last_value.gif

Purpose

LAST_VALUE is an analytic function. It returns the last value in an ordered set of values. If the last value in the set is null, then the function returns NULL unless you specify IGNORE NULLS. This setting is useful for data densification. If you specify IGNORE NULLS, then LAST_VALUE returns the fist non-null value in the set, or NULL if all values are null. Please refer to "Using Partitioned Outer Joins: Examples" for an example of data densification.

You cannot use LAST_VALUE or any other analytic function for expr. That is, you cannot nest analytic functions, but you can use other built-in function expressions for expr. Please refer to "About SQL Expressions" for information on valid forms of expr.

Examples

The following example returns, for each row, the hire date of the employee earning the highest salary:

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);

LAST_NAME                     SALARY HIRE_DATE LV
------------------------- ---------- --------- ---------
Kochhar                        17000 21-SEP-89 17-JUN-87
De Haan                        17000 13-JAN-93 17-JUN-87
King                           24000 17-JUN-87 17-JUN-87

This example illustrates the nondeterministic nature of the LAST_VALUE function. Kochhar and De Haan have the same salary, so they are in adjacent rows. Kochhar appears first because the rows in the subquery are ordered by hire_date. However, if the rows are ordered by hire_date in descending order, as in the next example, then the function returns a different value:

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 DESC);

LAST_NAME                     SALARY HIRE_DATE LV
------------------------- ---------- --------- ---------
De Haan                        17000 13-JAN-93 17-JUN-87
Kochhar                        17000 21-SEP-89 17-JUN-87
King                           24000 17-JUN-87 17-JUN-87

The following two examples show how to make the LAST_VALUE function deterministic by ordering on a unique key. By ordering within the function by both salary and hire_date, you can ensure the same result regardless of the ordering in the subquery.

SELECT last_name, salary, hire_date, LAST_VALUE(hire_date) OVER
(ORDER BY salary, hire_date 
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lv
FROM (SELECT * FROM employees WHERE department_id = 90 
   ORDER BY hire_date);

LAST_NAME                     SALARY HIRE_DATE LV
------------------------- ---------- --------- ---------
Kochhar                        17000 21-SEP-89 17-JUN-87
De Haan                        17000 13-JAN-93 17-JUN-87
King                           24000 17-JUN-87 17-JUN-87

SELECT last_name, salary, hire_date, LAST_VALUE(hire_date) OVER
   (ORDER BY salary, hire_date
   ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lv
FROM (SELECT * FROM employees WHERE department_id = 90 
   ORDER BY hire_date DESC);

LAST_NAME                     SALARY HIRE_DATE LV
------------------------- ---------- --------- ---------
Kochhar                        17000 21-SEP-89 17-JUN-87
De Haan                        17000 13-JAN-93 17-JUN-87
King                           24000 17-JUN-87 17-JUN-87