SQLite 3 reference · Window Functions
SQLite DENSE_RANK() Function
Rank rows by the window's ORDER BY, giving ties the same rank without gaps after them (1, 2, 2, 3).
SQLite 3 Since 3.25.0
Syntax
dense_rank() OVER (PARTITION BY ... ORDER BY ...)
Return value
An integer from 1.
Examples
SELECT name, title,
rank() OVER (ORDER BY title) AS rank,
dense_rank() OVER (ORDER BY title) AS dense
FROM employees
WHERE department = 'Engineering';
Output
name | title | rank | dense Omar Haddad | CTO | 1 | 1 Priya Nair | Engineer | 2 | 2 Lucas Martin | Engineer | 2 | 2 Mei Chen | Engineering Manager | 4 | 3 Jonas Berg | Senior Engineer | 5 | 4
SELECT DISTINCT salary, dense_rank() OVER (ORDER BY salary DESC) AS level
FROM employees
ORDER BY level
LIMIT 3;
Output
salary | level 250000 | 1 210000 | 2 165000 | 3
Version history
| Version | Change |
|---|---|
| SQLite 3.25.0 | Added |
In other languages
DENSE_RANK()· 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).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.- SQL Window Functions · tutorial