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> [ { ASC | DESC } ] [ <window_frame> ] )

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

Arguments

• <n>
  Specifies which value of N to use when looking for the Nth value.

• <expr>
  The expression that determines the return value.

• <expr1>
  The expression by which to partition the rows. You can specify a single expression or a comma-separated list of expressions. For example:

  PARTITION BY column_1, column_2

• <expr2>
  The expression by which to order the rows. You can specify a single expression or a comma-separated list of expressions. For example:

  ORDER BY column_3, column_4

• FROM { FIRST | LAST }
  Where to start counting for the nth value:

  • FROM FIRST starts from the beginning of the ordered list and moves forward.
  • FROM LAST starts from the end of the ordered list and moves backward.
    Default: FROM FIRST

• { IGNORE | RESPECT } NULLS
  Whether to ignore or respect NULL values when <expr> contains NULLs:

  • IGNORE NULLS skips NULL values in the expression.
  • RESPECT NULLS returns a NULL value if it is the nth value in the expression.
    Default: RESPECT NULLS

Usage notes

• Input value <n> can’t be greater than 1000.
• This function is a rank-related function, so it must specify a window. A window clause consists of the following subclauses:
  • PARTITION BY <expr1> subclause (optional).
  • ORDER BY <expr2> subclause (required). For details about additional supported ordering options (sort order, ordering of NULL values, etc.), see the documentation for the ORDER BY clause, which follows the same rules.
  • <window_frame> subclause (optional).
• The order of rows in a window (and thus the result of the query) is fully deterministic only if the keys in the ORDER BY clause make each row unique. Consider:

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

  The query result can vary if any partition contains values of column o that are identical, or would be identical in a case-insensitive comparison.
• The ORDER BY clause inside the OVER clause controls the order of rows only within the window, not the order of rows in the output of the entire query. To control output order, use a separate ORDER BY clause at the outermost level of the query.
• 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 information about window frames, including syntax, usage notes, and examples, see Window function syntax and usage.

Examples

Example 1: Return the second value in order (per partition):

SELECT column1,
       column2,
       NTH_VALUE(column2, 2) OVER (PARTITION BY column1 ORDER BY column2) AS column2_2nd
  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
  ) t;

Output:

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

---

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.

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 |
+---------------+-----------+---+-----------+---------+----------+