NTH_VALUE
Returns the value of column from the n-th row of the window frame.
PostgreSQL 17.5
NTH_VALUE(column, n) OVER ([PARTITION BY ...] [ORDER BY ...] [frame])Parameters
- column
- Column or expression whose value is returned
- ninteger
- Row number in the frame, starting from 1
- PARTITION BYoptional
- Columns that split rows into partitions
- ORDER BYoptional
- Order of rows in the partition
- framekeywordoptional
- Window frame, with ORDER BY 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 with the default frame
PostgreSQL 17.5
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.5
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
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.