Categories:

Aggregate Functions (General) , Window Functions (Analytic, Window Frame)

NTH_VALUE

Returns the nth value (up to 1000) within an ordered group of values.

See also:

FIRST_VALUE , LAST_VALUE

Syntax

NTH_VALUE( <expr> , n ) [ FROM { FIRST | LAST } ] [ { IGNORE | RESPECT } NULLS ]
                        OVER ( [ PARTITION BY <expr1> ] ORDER BY <expr2> [ <window_frame> ] )

Usage Notes

  • Input value n cannot be greater than 1000.

  • If FROM { FIRST | LAST } is not specified, the default is FIRST, i.e. the direction is from the beginning of the ordered list.

  • 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 nth value in the expression.

  • NTH_VALUE is an analytic 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 Frames.

Examples

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

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