NTH_VALUE
Returns the value of column from the n-th row of the window frame.
NTH_VALUE(column, n) OVER ([PARTITION BY ...] [ORDER BY ...] [frame])Parameters
- column
- Column or expression whose value is returned
- nINT
- Row number in the frame, starting from 1
- PARTITION BYoptional
- Columns that split rows into partitions
- ORDER BYoptional
- Order of rows in the partition that defines which row is n-th
- framekeywordoptional
- Window frame: ROWS or RANGE BETWEEN start AND end; from the start of the partition to the current row by default
Return value
A value of the same type as column; NULL if the frame has fewer than n rows
Examples
Second oldest
SELECT member_name,
birthday,
NTH_VALUE(member_name, 2) OVER (
ORDER BY birthday
) AS second_oldest
FROM FamilyMembers;Second salary in each department
SELECT department_id,
name,
salary,
NTH_VALUE(salary, 2) OVER (
PARTITION BY department_id
ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS second_salary
FROM employees;Details
With the default frame, the n-th row is visible only starting from itself: the first n - 1 rows of the partition get NULL. To get the value for all rows, set the frame to the whole partition. NTH_VALUE(column, 1) is the same as FIRST_VALUE(column).
The frame sets which rows of the partition the function sees and is written after ORDER BY: ROWS BETWEEN start AND end counts rows, while RANGE BETWEEN start AND end uses the ORDER BY values.
Without a frame, the default rule applies. If OVER has ORDER BY, the frame runs from the start of the partition to the current row, including rows with the same ORDER BY value. Without ORDER BY, the frame is the whole partition.