SQLite 3 reference · Window Functions
SQLite RANK() Function
Rank rows by the window's ORDER BY, giving ties the same rank and leaving gaps after them (1, 2, 2, 4).
SQLite 3 Since 3.25.0
Syntax
rank() OVER (PARTITION BY ... ORDER BY ...)
Return value
An integer from 1.
Examples
SELECT name, title, rank() OVER (ORDER BY title) AS title_rank
FROM employees
WHERE department = 'Engineering';
Output
name | title | title_rank Omar Haddad | CTO | 1 Priya Nair | Engineer | 2 Lucas Martin | Engineer | 2 Mei Chen | Engineering Manager | 4 Jonas Berg | Senior Engineer | 5
SELECT name, unit_price, rank() OVER (ORDER BY unit_price DESC) AS r
FROM order_items JOIN products USING (product_id)
WHERE order_id = 7;
Output
name | unit_price | r The Pragmatic Programmer | 42 | 1 Wireless Mouse | 24.99 | 2 Coffee Mug | 12 | 3
Version history
| Version | Change |
|---|---|
| SQLite 3.25.0 | Added |
In other languages
RANK()· PostgreSQL
Related
DENSE_RANK()· Rank rows by the window's ORDER BY, giving ties the same rank without gaps after them (1, 2, 2, 3).ROW_NUMBER()· Number the rows of a window 1, 2, 3, … in the order given by the window's ORDER BY. Ties get different numbers.PERCENT_RANK()· Return a row's relative rank from 0 to 1: (rank - 1) / (rows in partition - 1).- SQL Window Functions · tutorial