NTH_VALUE() OVER ...
All functions > WINDOW FUNCTION > NTH_VALUE() OVER ...
Returns the value at the nth row of the window frame.
Syntax
Diagram(
Sequence(
Terminal("NTH_VALUE"),
Terminal("("),NonTerminal('expr'), Terminal(','), NonTerminal('n'),Terminal(")"),
Terminal("OVER"),
Terminal("("),
NonTerminal("over_clause"),
Terminal(")"),
)
)| Parameter | Type | Required | Description |
|---|---|---|---|
expr | EXPRESSION | Yes | Expression to get the nth value of |
n | BIGINT | Yes | The position (1-based) of the value to return |
over_clause | OVER CLAUSE | Yes | OVER (PARTITION BY ... ORDER BY ... [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