nth_value analytic window function

  • Article

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Returns the value at a specific offset in the window.

Syntax

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

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.
  • OVER clause: The clause describing the windowing. See: Window functions.

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