`NTH_VALUE`

Applies to: ✅ Data 360 SQL ✅ Tableau Hyper API

Returns <value> evaluated at the row that’s the <nth> row of the window frame, counting from either the first or the last row in the frame. Returns NULL if the row doesn’t exist.

Syntax

1nth_value(<value>, <nth>) [ FROM FIRST | FROM LAST ] OVER (
2  [ PARTITION BY <expression> [, ...] ]
3  [ ORDER BY <expression> [, ...] ]
4  [ <frame_clause> ]
5)

Arguments

Required

<value>: The expression to evaluate at the <nth> row of the window frame.
<nth>: An integer specifying which row of the frame to evaluate (1-based).

Optional

FROM FIRST / FROM LAST: Specifies whether <nth> is counted from the first row or the last row of the frame. FROM FIRST or FROM LAST options are valid only for the nth_value function. Default: FROM FIRST.

Returns

Returns the same type as <value>.

Null handling:

Returns NULL if there’s no such row, for example if <nth> exceeds the number of rows in the frame.

Considerations

Similar to first_value and last_value, nth_value considers only the rows within the window frame, not the whole partition. By default, the frame contains the rows from the start of the partition through the last peer of the current row. Whether a given <nth> row exists in the frame, and the result, can change from row to row as ORDER BY moves through the partition.
To get a fixed <nth> value across the entire partition regardless of the current row’s position, add an explicit frame such as ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to the OVER clause.
For full details on PARTITION BY, ORDER BY, and frame specifications, see Window Function Call Syntax. To name a window definition for reuse, see the WINDOW clause in SELECT.

Examples

Second-Highest Salary per Department, Counting from First

Uses an explicit ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING frame so the frame covers the whole partition, then returns the second-highest salary (FROM FIRST is the default) for every row in each department.

1SELECT depname, empno, salary,
2  nth_value(salary, 2) OVER (PARTITION BY depname ORDER BY salary DESC
3    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS second_highest
4FROM empsalary;

Second-Lowest Salary per Department, Counting from Last

Uses FROM LAST to count from the end of the frame instead of the start. Returns the second-lowest salary in each department.

1SELECT depname, empno, salary,
2  nth_value(salary, 2) FROM LAST OVER (PARTITION BY depname ORDER BY salary DESC
3    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS second_lowest
4FROM empsalary;

DID THIS ARTICLE SOLVE YOUR ISSUE?
Let us know so we can improve!

NTH_VALUE