LAST_VALUE [Analytic]

Lets you select the last value of a table or partition (determined by the window-order-clause) without having to use a self join. LAST_VALUE takes the last record from the partition after the window order clause. The function then computes the expression against the last record, and returns the results. This function is useful when you want to use the last value as a baseline in calculations.

Use LAST_VALUE() with the window-order-clause to produce deterministic results. If no window is specified for the current row, the default window is UNBOUNDED PRECEDING AND CURRENT ROW.

Due to default window semantics, LAST_VALUE does not always return the last value of a partition. If you omit Window Frame Clause from the analytic clause, LAST_VALUE operates on this default window. Although results can seem non-intuitive by not returning the bottom of the current partition, it returns the bottom of the window, which continues to change along with the current input row being processed. If you want to return the last value of a partition, use UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. See examples below.

Behavior Type

Immutable

Syntax

LAST_VALUE ( expression [ IGNORE NULLS ] ) OVER (
    [ window-partition-clause ] 
    [ window-order-clause ]
    [ window-frame-clause ] )

Parameters

expression

Expression to evaluate—for example, a constant, column, nonanalytic function, function expression, or expressions involving any of these.
IGNORE NULLS

Specifies to return the last non-null value in the set, or NULL if all values are NULL. If you omit this option and the last value in the set is null, the function returns NULL.
OVER()

See Analytic Functions.

Example

Using the schema defined in Window Framing in Analyzing Data, the following query does not show the highest salary value by department; instead it shows the highest salary value by department by salary.

=> SELECT deptno, sal, empno, LAST_VALUE(sal)
       OVER (PARTITION BY deptno ORDER BY sal) AS lv 
   FROM emp;
 deptno | sal | empno |    lv 
--------+-----+-------+--------
     10 | 101 |     1 |     101
     10 | 104 |     4 |     104
     20 | 100 |    11 |     100
     20 | 109 |     7 |     109  
     20 | 109 |     6 |     109  
     20 | 109 |     8 |     109
     20 | 110 |    10 |     110  
     20 | 110 |     9 |     110  
     30 | 102 |     2 |     102
     30 | 103 |     3 |     103
     30 | 105 |     5 |     105

If you include the window frame clause ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, LAST_VALUE() returns the highest salary by department, an accurate representation of the information:

=> SELECT deptno, sal, empno, LAST_VALUE(sal)
       OVER (PARTITION BY deptno ORDER BY sal 
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lv 
   FROM emp;
 deptno | sal | empno |    lv 
--------+-----+-------+--------
     10 | 101 |     1 |     104
     10 | 104 |     4 |     104
     20 | 100 |    11 |     110
     20 | 109 |     7 |     110  
     20 | 109 |     6 |     110  
     20 | 109 |     8 |     110
     20 | 110 |    10 |     110  
     20 | 110 |     9 |     110  
     30 | 102 |     2 |     105
     30 | 103 |     3 |     105
     30 | 105 |     5 |     105

For more examples, see FIRST_VALUE().

See Also