PostgreSQL 18 reference · Window Functions
PostgreSQL NTH_VALUE() Function
Return a value from the nth row of the window frame, or NULL if the frame has fewer rows.
PostgreSQL 18 Since 8.4
Syntax
nth_value(value, n) OVER (PARTITION BY ... ORDER BY ...)
Parameters
| Parameter | Description |
|---|---|
value |
The expression to read. |
n |
The row within the frame, from 1. |
Return value
The value, or NULL.
Examples
SELECT DISTINCT nth_value(name, 2) OVER (ORDER BY salary DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS second_highest_paid
FROM employees;
Output
second_highest_paid --------------------- Omar Haddad (1 row)
Run this on your computer — see “Get Started” for setup.
SELECT name, nth_value(name, 3) OVER (ORDER BY hire_date) AS third_hire
FROM employees
LIMIT 4;
Output
name | third_hire ---------------+--------------- Sofia Reyes | NULL Omar Haddad | NULL Hannah Okafor | Hannah Okafor Mei Chen | Hannah Okafor (4 rows)
Run this on your computer — see “Get Started” for setup.
Version history
| Version | Change |
|---|---|
| PostgreSQL 8.4 | Added |
In other languages
NTH_VALUE()· SQLite
Related
FIRST_VALUE()· Return a value from the first row of the window frame, such as each department's highest salary.LAST_VALUE()· Return a value from the last row of the window frame. Widen the frame or it returns the current row.- SQL Window Functions · tutorial