SQL Reference ยท Window Functions
SQL RANK() and DENSE_RANK() functions
Reference for SQL RANK() and DENSE_RANK(): rank rows with ties, understand gaps versus no gaps, rank within groups, NULL ordering and top-N with ties.
RANK() and DENSE_RANK() are window functions that assign a rank to each row according to the ORDER BY of the window. Rows with equal sort values are peers and get the same rank. The two functions differ in what comes after a tie.
RANK() leaves gaps: after two rows tied for rank 2, the next rank is 4. DENSE_RANK() never skips: the next rank is 3. Both start at 1 and restart in every PARTITION BY group. If you need a unique number for every row, use ROW_NUMBER().
SELECT name, price, RANK() OVER (ORDER BY price DESC) AS rnk, DENSE_RANK() OVER (ORDER BY price DESC) AS dense_rnk FROM products;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
RANK() OVER ([PARTITION BY ...] ORDER BY expression [ASC | DESC] [, ...]) DENSE_RANK() OVER ([PARTITION BY ...] ORDER BY expression [ASC | DESC] [, ...])
| Part | Description |
|---|---|
PARTITION BY |
Optional. Ranks restart at 1 in each partition. |
ORDER BY |
Required in practice. Rows are ranked by these expressions; rows equal on all of them are peers. |
Returns: an integer rank starting at 1. RANK() returns one plus the number of rows that sort strictly before the current row. DENSE_RANK() returns one plus the number of distinct sort values before it.
Dialect differences
| Feature | SQLite | PostgreSQL | MySQL | SQL Server | Oracle |
|---|---|---|---|---|---|
RANK(), DENSE_RANK() |
3.25+ | 8.4+ | 8.0+ | 2005+ | Yes |
OVER without ORDER BY |
All rows get rank 1 | All rows get rank 1 | All rows get rank 1 | Error | Error |
NULLs in ORDER BY |
Sort first ascending | Sort last ascending | Sort first ascending | Sort first ascending | Sort last ascending |
Hypothetical form RANK(value) WITHIN GROUP (...) |
No | Yes | No | No | Yes |
-- PostgreSQL, Oracle: what rank would 70000 have among current salaries? SELECT RANK(70000) WITHIN GROUP (ORDER BY salary) FROM employees; -- PostgreSQL, Oracle: control where NULLs rank SELECT RANK() OVER (ORDER BY manager_id NULLS LAST) FROM employees;
Usage notes
- Use
DENSE_RANK()for "the third highest value".RANK()can skip the number 3 entirely when two rows tie for second place. Dense ranks always have a row for every number up to the maximum. - Use
RANK()for competition-style standings (1, 2, 2, 4), where the position reflects how many rows came before. - Top N with ties: filter
rnk <= Nin an outer query. The result may have more than N rows when ties cross the boundary. For exactly N rows useROW_NUMBER()with a tie-breaker. - Peers are decided by every
ORDER BYexpression. Adding a second sort column breaks ties and changes the ranks. NULLhandling:NULLs are peers of each other. Where they sort differs by database (see the table), so writeNULLS FIRST/LASTwhere supported or sortNULLs explicitly withCASE.- You cannot use the rank in
WHEREin the same query. Wrap it in a subquery or CTE. - Ascending vs descending:
ORDER BY price DESCgives rank 1 to the highest price. ForgettingDESCis the most common bug. - Percent-style alternatives:
PERCENT_RANK()andCUME_DIST()express rank as a fraction between 0 and 1.