SQLite 3 reference · Window Functions
SQLite NTH_VALUE() Function
Return a value from the Nth row of the window frame, or NULL if the frame has fewer rows.
SQLite 3 Since 3.25.0
Syntax
nth_value(X, N) OVER (PARTITION BY ... ORDER BY ...)
Parameters
| Parameter | Description |
|---|---|
X |
A column or expression. |
N |
The row number 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
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
Version history
| Version | Change |
|---|---|
| SQLite 3.25.0 | Added |
In other languages
NTH_VALUE()· PostgreSQL
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 to see the real last row of the partition.- SQL Window Functions · tutorial