Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-01 |
|
|
View PDF |
Syntax
See Also: "Analytic Functions" for information on syntax, semantics, and restrictions, including valid forms ofexpr |
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