LAST_VALUE

Syntax

For information on syntax and semantics, see "Analytic Functions".

Purpose

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

Examples

The following example returns the hiredate of the employee earning the highest salary.

SELECT ename, sal, hiredate, LAST_VALUE(hiredate) OVER
   (ORDER BY sal 
      ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS lv
FROM (SELECT * FROM emp WHERE deptno=20 ORDER BY hiredate);

ENAME             SAL HIREDATE  LV
---------- ---------- --------- ---------
SMITH             800 17-DEC-80 19-APR-87
ADAMS            1100 23-MAY-87 19-APR-87
JONES            2975 02-APR-81 19-APR-87
FORD             3000 03-DEC-81 19-APR-87
SCOTT            3000 19-APR-87 19-APR-87

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

SELECT ename, sal, hiredate, LAST_VALUE(hiredate) OVER
   (ORDER BY sal 
      ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS lv
FROM (SELECT * FROM emp WHERE deptno=20 ORDER BY hiredate DESC);

ENAME             SAL HIREDATE  LV
---------- ---------- --------- ---------
SMITH             800 17-DEC-80 03-DEC-81
ADAMS            1100 23-MAY-87 03-DEC-81
JONES            2975 02-APR-81 03-DEC-81
SCOTT            3000 19-APR-87 03-DEC-81
FORD             3000 03-DEC-81 03-DEC-81

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 hiredate, you can ensure the same result regardless of the ordering in the subquery.

SELECT ename, sal, hiredate, LAST_VALUE(hiredate) OVER
   (ORDER BY sal, hiredate 
      ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS lv
FROM (SELECT * FROM emp WHERE deptno=20 ORDER BY hiredate);

ENAME             SAL HIREDATE  LV
---------- ---------- --------- ---------
SMITH             800 17-DEC-80 19-APR-87
ADAMS            1100 23-MAY-87 19-APR-87
JONES            2975 02-APR-81 19-APR-87
FORD             3000 03-DEC-81 19-APR-87
SCOTT            3000 19-APR-87 19-APR-87

SELECT ename, sal, hiredate, LAST_VALUE(hiredate) OVER
   (ORDER BY sal, hiredate 
      ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS lv
FROM (SELECT * FROM emp WHERE deptno=20 ORDER BY hiredate DESC);

ENAME             SAL HIREDATE  LV
---------- ---------- --------- ---------
SMITH             800 17-DEC-80 19-APR-87
ADAMS            1100 23-MAY-87 19-APR-87
JONES            2975 02-APR-81 19-APR-87
FORD             3000 03-DEC-81 19-APR-87
SCOTT            3000 19-APR-87 19-APR-87