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.

Examples¶

SELECT
    column1,
    column2,
    FIRST_VALUE(column2) OVER (PARTITION BY column1 ORDER BY column2) AS column2_first
FROM VALUES
   (1, 10), (1, 11), (1, null), (1, 12),
   (2, 20), (2, 21), (2, 22);

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