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) OVER ([PARTITION BY expr [, ...]] [ORDER BY sort_item [, ...]] [ROWS|RANGE|GROUPS frame])

NTH_VALUE(expr, n) 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

• OVER clause

Examples

SELECT
    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 row by id order (v=10 on id=2) with a full partition frame
;