Oracle Analytical Function LAST_VALUE With Example

LAST_VALUE
--------------------

LAST_VALUE is an analytic function that is useful for data densification. It returns the last value in an ordered set of values

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
--------------- ---------- --------- ---------
De Haan              17000 13-JAN-01 17-JUN-03
Kochhar              17000 21-SEP-05 17-JUN-03
King                 24000 17-JUN-03 17-JUN-03

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
--------------- ---------- --------- ---------
Kochhar              17000 21-SEP-05 17-JUN-03
De Haan              17000 13-JAN-01 17-JUN-03
King                 24000 17-JUN-03 17-JUN-03

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)
  ORDER BY last_name, salary, hire_date;

LAST_NAME           SALARY HIRE_DATE LV
--------------- ---------- --------- ---------
De Haan              17000 13-JAN-01 17-JUN-03
King                 24000 17-JUN-03 17-JUN-03
Kochhar              17000 21-SEP-05 17-JUN-03

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)
  ORDER BY last_name, salary, hire_date;

LAST_NAME           SALARY HIRE_DATE LV
--------------- ---------- --------- ---------
De Haan              17000 13-JAN-01 17-JUN-03
King                 24000 17-JUN-03 17-JUN-03
Kochhar              17000 21-SEP-05 17-JUN-03

When you use a logical offset (RANGE instead of ROWS), the function is deterministic. When duplicates are found for the ORDER BY expression, the LAST_VALUE is the highest value of expr:

SELECT last_name, salary, hire_date,
       LAST_VALUE(hire_date)
         OVER (ORDER BY salary RANGE 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-01 17-JUN-03
Kochhar                        17000 21-SEP-05 17-JUN-03
King                           24000 17-JUN-03 17-JUN-03