SQLite 3 reference · Window Functions
SQLite ROW_NUMBER() Function
Number the rows of a window 1, 2, 3, … in the order given by the window's ORDER BY. Ties get different numbers.
SQLite 3 Since 3.25.0
Syntax
row_number() OVER (PARTITION BY ... ORDER BY ...)
Return value
An integer from 1.
Examples
SELECT row_number() OVER (ORDER BY price DESC) AS n, name, price
FROM products
LIMIT 3;
Output
n | name | price 1 | 4K Monitor | 329 2 | Robot Kit | 120 3 | Mechanical Keyboard | 89
SELECT department, name, salary
FROM (
SELECT *, row_number() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
FROM employees
)
WHERE rn = 1;
Output
department | name | salary Engineering | Omar Haddad | 210000 Management | Sofia Reyes | 250000 Sales | Hannah Okafor | 150000 Support | Ravi Kumar | 65000
Version history
| Version | Change |
|---|---|
| SQLite 3.25.0 | Added |
In other languages
ROW_NUMBER()· PostgreSQL
Related
RANK()· Rank rows by the window's ORDER BY, giving ties the same rank and leaving gaps after them (1, 2, 2, 4).DENSE_RANK()· Rank rows by the window's ORDER BY, giving ties the same rank without gaps after them (1, 2, 2, 3).NTILE()· Split the rows of a window into N groups of nearly equal size and return each row's group number.- SQL Window Functions · tutorial