NTH_VALUE
Returns the value of column from the n-th row of the window frame.
PostgreSQL 17.9
NTH_VALUE(column, n)- column
- Column or expression whose value is returned
- n
- Row number in the frame, starting from 1integer
Return value
A value of the same type as column; NULL if the frame has fewer than n rows
Examples
Second oldest with the default frame
PostgreSQL 17.9
SELECT member_name,
birthday,
NTH_VALUE(member_name, 2) OVER (
ORDER BY birthday
) AS second_oldest
FROM FamilyMembers;Second oldest in the whole partition
PostgreSQL 17.9
SELECT member_name,
birthday,
NTH_VALUE(member_name, 2) OVER (
ORDER BY birthday ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS second_oldest
FROM FamilyMembers;Details
The default frame runs from the start of the partition to the current row and rows with the same ORDER BY value, and without ORDER BY it is the whole partition. So with the default frame the first n − 1 rows of a partition get NULL: the n-th row is not in their frame yet. To give all rows the same value, set the frame to the whole partition: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.