NTH_VALUE() OVER ...

All functions > WINDOW FUNCTION > NTH_VALUE() OVER ...

Returns the value at the nth row of the window frame.

Syntax

NTH_VALUE(expr, n) [WITHIN (WHERE condition)] OVER ([PARTITION BY expr [, ...]] [ORDER BY sort_item [, ...]] [ROWS|RANGE|GROUPS frame])

Notes

  • Returns the value of the expression at the nth row of the window frame
  • n is 1-based (1 = first row, 2 = second row, etc.)
  • Returns NULL if the nth row does not exist in the frame
  • Requires a window frame specification for predictable results
  • Use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to consider all rows

See also

Examples

FeatureQL
SELECT
    -- Second row by id order (v=10 on id=2) with a full partition frame
    f1 := ZIP(ARRAY(1, 2, 3) AS id, ARRAY(30, 10, 20) AS v).TRANSFORM(
        SELECT NTH_VALUE(v, 2) OVER (
            ORDER BY id ASC
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        )
    ).UNWRAP(),
    -- Second matching row by id order with a full partition frame; rows outside WITHIN return NULL
    f2 := ZIP(
        ARRAY(1, 2, 3, 4) AS id,
        ARRAY(10, 20, 30, 40) AS v,
        ARRAY(TRUE, FALSE, TRUE, TRUE) AS keep
    ).TRANSFORM(
        SELECT NTH_VALUE(v, 2) WITHIN (WHERE keep) OVER (
            ORDER BY id ASC
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        )
    ).UNWRAP()
;
Result
f1 ARRAYf2 ARRAY
[10, 10, 10][30, null, 30, 30]

Suggest changes to this page
Last update at: 2026/06/20 10:08:10