nth_value analytic window function (Databricks SQL)

Returns the value at a specific offset in the window.

Syntax

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

Arguments

  • 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.

Returns

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 (Databricks SQL) clause with this function. If the order is non-unique, the result is non-deterministic.

Examples

> 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