LAST_VALUE

Syntax

last_value::=

Text description of last_value

See Also: "Analytic Functions" for information on syntax, semantics, and restrictions

Purpose

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

You cannot use LAST_VALUE or any other analytic function for expr. That is, you can use other built-in function expressions for expr, but you cannot nest analytic functions.

See Also: "About SQL Expressions" for information on valid forms of expr

Examples

The following example returns, for each row, the hiredate 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, 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