LAST_VALUE

Returns the last value within an ordered group of values.

See also: FIRST_VALUE, NTH_VALUE

Syntax

LAST_VALUE( <expr> ) [ { IGNORE | RESPECT } NULLS ]
  OVER (
    [ PARTITION BY <expr1> ]
    ORDER BY <expr2> [ { ASC | DESC } ] [ NULLS { FIRST | LAST } ]
    [ <window_frame> ]
  )

For detailed <window_frame> syntax, see Window function syntax and usage.

Arguments

• expr: The expression that determines the return value.
• expr1: The expression(s) by which to partition the rows. You can specify a single expression or a comma-separated list. Example:

  PARTITION BY column_1, column_2

• expr2: The expression(s) by which to order the rows. You can specify a single expression or a comma-separated list. Example:

  ORDER BY column_3, column_4

• { IGNORE | RESPECT } NULLS: Whether to ignore or respect NULL values when an expr contains NULL values:
  • IGNORE NULLS: returns the last non-NULL value in the frame.
  • RESPECT NULLS: returns NULL if NULL is the last value in the frame.
  • Default: RESPECT NULLS.

Usage notes

• This function is a rank-related window function; it must specify a window via the OVER clause. The window clause consists of:
  • Optional PARTITION BY expr1
  • Required ORDER BY expr2 (sorting options and NULL ordering follow the same rules as the standard ORDER BY clause)
  • Optional window_frame
• The order of rows in a window (and thus the function result) is fully deterministic only if the keys in the ORDER BY clause make each row unique. For example:

  ... OVER (PARTITION BY p ORDER BY o COLLATE 'lower') ...

  Results can vary if a partition contains identical values of o (or effectively identical under the collation).
• The ORDER BY inside OVER controls ordering within the window only; it does not control the final output order. Use an outer ORDER BY to sort the query result set.
• If no window frame is specified, the default frame for this ranking function is the entire window:

  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

  This differs from the ANSI standard, which specifies:

  RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

For detailed <window_frame> syntax, see Window function syntax and usage.

Examples

Example 1: Simple LAST_VALUE per partition

The first example returns LAST_VALUE results for column2 partitioned by column1:

SELECT
    column1,
    column2,
    LAST_VALUE(column2) OVER (
      PARTITION BY column1
      ORDER BY column2
    ) AS column2_last
FROM (
    SELECT 1 AS column1, 10 AS column2 UNION ALL
    SELECT 1 AS column1, 11 AS column2 UNION ALL
    SELECT 1 AS column1, 12 AS column2 UNION ALL
    SELECT 2 AS column1, 20 AS column2 UNION ALL
    SELECT 2 AS column1, 21 AS column2 UNION ALL
    SELECT 2 AS column1, 22 AS column2
);

Output:

+---------+---------+--------------+
| COLUMN1 | COLUMN2 | COLUMN2_LAST |
|---------+---------+--------------|
|       1 |      10 |           12 |
|       1 |      11 |           12 |
|       1 |      12 |           12 |
|       2 |      20 |           22 |
|       2 |      21 |           22 |
|       2 |      22 |           22 |
+---------+---------+--------------+

---

Example 2: Comparing FIRST_VALUE, NTH_VALUE, and LAST_VALUE with a sliding frame

The query creates a sliding window frame that is three rows wide, which contains:

• The row that precedes the current row.

• The current row.

• The row that follows the current row.

The 2 in the call NTH_VALUE(i, 2) specifies the second row in the window frame (which, in this case, is also the current row).

When the current row is the very first row in the window frame, there is no preceding row to reference, so FIRST_VALUE returns a NULL for that row.

Frame boundaries sometimes extend beyond the rows in a partition, but non-existent rows are not included in window function calculations. For example, when the current row is the very first row in the partition and the window frame is ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING, there is no preceding row to reference, so the FIRST_VALUE function returns the value of the first row in the partition.

The results never match for all three functions, given the data in the table. These functions select the first, last, or nth value for each row in the frame, and the selection of values applies separately to each partition.

To run this example, first create and load the table:

Run the following query:

SELECT partition_col, order_col, i,
       FIRST_VALUE(i)  OVER (PARTITION BY partition_col ORDER BY order_col
         ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS FIRST_VAL,
       NTH_VALUE(i, 2) OVER (PARTITION BY partition_col ORDER BY order_col
         ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS NTH_VAL,
       LAST_VALUE(i)   OVER (PARTITION BY partition_col ORDER BY order_col
         ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS LAST_VAL
  FROM (
        SELECT 1 AS i, 1 AS partition_col, 1 AS order_col UNION ALL
        SELECT 2 AS i, 1 AS partition_col, 2 AS order_col UNION ALL
        SELECT 3 AS i, 1 AS partition_col, 3 AS order_col UNION ALL
        SELECT 4 AS i, 1 AS partition_col, 4 AS order_col UNION ALL
        SELECT 5 AS i, 1 AS partition_col, 5 AS order_col UNION ALL
        SELECT 1 AS i, 2 AS partition_col, 1 AS order_col UNION ALL
        SELECT 2 AS i, 2 AS partition_col, 2 AS order_col UNION ALL
        SELECT 3 AS i, 2 AS partition_col, 3 AS order_col UNION ALL
        SELECT 4 AS i, 2 AS partition_col, 4 AS order_col
  ) 
  ORDER BY partition_col, i, order_col;

Output:

+---------------+-----------+---+-----------+---------+----------+
| PARTITION_COL | ORDER_COL | I | FIRST_VAL | NTH_VAL | LAST_VAL |
|---------------+-----------+---+-----------+---------+----------|
|             1 |         1 | 1 |         1 |       2 |        2 |
|             1 |         2 | 2 |         1 |       2 |        3 |
|             1 |         3 | 3 |         2 |       3 |        4 |
|             1 |         4 | 4 |         3 |       4 |        5 |
|             1 |         5 | 5 |         4 |       5 |        5 |
|             2 |         1 | 1 |         1 |       2 |        2 |
|             2 |         2 | 2 |         1 |       2 |        3 |
|             2 |         3 | 3 |         2 |       3 |        4 |
|             2 |         4 | 4 |         3 |       4 |        4 |
+---------------+-----------+---+-----------+---------+----------+