nth_value analytic window function

Returns the value at a specific offset in the window.


nth_value(expr, offset) [ IGNORE NULLS | RESPECT NULLS ]


  • expr: An expression of any type.

  • offset: An INTEGER literal greater than 0.

  • IGNORE NULLS or RESPECT NULLS: When IGNORE NULLS is used any expr value that is NULL is ignored in the count. The default is RESPECT NULLS.


The result type matches the expr type.

The window function returns the value of expr at the row that is the offsetth row from the beginning of the window frame.

If there is no such offsetth row, returns NULL.

You must use the ORDER BY clause clause with this function. If the order is non-unique, the result is non-deterministic.


> SELECT a, b, nth_value(b, 2) OVER (PARTITION BY a ORDER BY b)
    FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b);
 A1 1   1
 A1 1   1
 A1 2   1
 A2 3   NULL