Documentation

    nth_value

    Retrieve a value at a specified position within a window frame.

    Try an expression

    In Formula, enter the expression and set Output type to INTEGER.

    Example input

    1. Row 1id = 1, amount = 10
    2. Row 2id = 2, amount = NULL
    3. Row 3id = 3, amount = 30
    4. Row 4id = 4, amount = 30

    Expression

    nth_value(amount, 2 IGNORE NULLS) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

    Result

    Row 1: 30
    Row 2: 30
    Row 3: 30
    Row 4: 30

    NULL means the value is missing.

    Arguments and types

    nth_value(expr, nth[ ORDER BY ordering][ IGNORE NULLS])

    The result type can depend on the input types. See DuckDB for the full argument rules.

    Things to know

    ORDER BY defines the order used by the calculation; it doesn't sort the output table. Include a unique tie-breaker when row position matters. Window expressions belong in Formula and can't be entered directly in Filter.

    Missing values occupy a position by default

    nth_value(amount, 2) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

    Every row: NULL

    Browse across rowsDuckDB documentation

    Function metadata from DuckDB, used under the MIT license.