LAST_VALUE

"Analytic Functions " for information on syntax, semantics, and restrictions, including valid forms of expr

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 IGNORENULLS. 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:

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:

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