Categories:

Window Functions (Rank-related, Window Frame)

FIRST_VALUE

Returns the first value within an ordered group of values.

See also:

LAST_VALUE , NTH_VALUE

Syntax

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

For details about window_frame syntax, see Window Frame Syntax and Usage.

Usage Notes

  • For compatibility with implementations of this function in other systems, { IGNORE | RESPECT } NULLS can also be specified in the arguments for the function:

    FIRST_VALUE( <expr> [ { IGNORE | RESPECT } NULLS ] ) OVER ...

  • If { IGNORE | RESPECT } NULLS is not specified, the default is RESPECT NULLS (i.e. a NULL value will be returned if the expression contains a NULL value and it is the first value in the expression).

  • FIRST_VALUE is a rank-related function so it requires a window to be specified, consisting of the following elements:

    • PARTITION BY <expr1> subclause (optional).

    • ORDER BY <expr2> subclause (required) with an optional window_frame. For more details about additional supported ordering options (sort order, ordering of NULL values, etc.), see the ORDER BY query construct.

  • The optional window_frame (cumulative or sliding) specifies the subset of rows within the window for which the function is calculated. If no window_frame is specified, the default is the entire window:

    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

    Note that this deviates from the ANSI standard, which specifies the following default for window frames:

    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

    For more details about window frames, including syntax and examples, see Window Frame Syntax and Usage.

  • The ORDER BY in the FIRST_VALUE clause affects only the order of the rows in the FIRST_VALUE clause; it does not control the order of the results of the entire query.

Examples

This shows a simple query using FIRST_VALUE(). This query contains two ORDER BY sub-clauses, one to control the order of rows in each partition, and one to control the order of the output of the full query.

SELECT
        column1,
        column2,
        FIRST_VALUE(column2) OVER (PARTITION BY column1 ORDER BY column2 NULLS LAST) AS column2_first
    FROM VALUES
       (1, 10), (1, 11), (1, null), (1, 12),
       (2, 20), (2, 21), (2, 22)
    ORDER BY column1, column2;
+---------+---------+---------------+
| COLUMN1 | COLUMN2 | COLUMN2_FIRST |
|---------+---------+---------------|
|       1 |      10 |            10 |
|       1 |      11 |            10 |
|       1 |      12 |            10 |
|       1 |    NULL |            10 |
|       2 |      20 |            20 |
|       2 |      21 |            20 |
|       2 |      22 |            20 |
+---------+---------+---------------+

The next query contrasts the outputs of FIRST_VALUE, NTH_VALUE, and LAST_VALUE. Note that:

  • The query creates a sliding window frame that is 3 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.

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 demo1
    ORDER BY partition_col, i, order_col;
+---------------+-----------+---+-----------+---------+----------+
| PARTITION_COL | ORDER_COL | I | FIRST_VAL | NTH_VAL | LAST_VAL |
|---------------+-----------+---+-----------+---------+----------|
|             1 |         1 | 1 |      NULL |       1 |        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 |      NULL |       1 |        2 |
|             2 |         2 | 2 |         1 |       2 |        3 |
|             2 |         3 | 3 |         2 |       3 |        4 |
|             2 |         4 | 4 |         3 |       4 |        4 |
+---------------+-----------+---+-----------+---------+----------+