NTH_VALUE
Returns the value of column from the n-th row of the window frame.
NTH_VALUE(column, n)- column
- Column or expression whose value is returned
- n
- Row number in the frame, starting from 1INT
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 frame_start AND frame_end counts rows, while RANGE BETWEEN frame_start AND frame_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.