SQL ROW_NUMBER, RANK, DENSE_RANK
Number or rank rows within groups.
Syntax
ROW_NUMBER() OVER (PARTITION BY … ORDER BY …) RANK() OVER (…) DENSE_RANK() OVER (…)
ROW_NUMBER gives 1, 2, 3 with no ties; RANK leaves gaps after ties (1, 2, 2, 4); DENSE_RANK does not (1, 2, 2, 3). Filter on them in an outer query or CTE.
Example
Query (on the shop database used in the SQL course)
SELECT category, name, price, ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn FROM products ORDER BY category, rn;
Result
| category | name | price | rn |
|---|---|---|---|
| Books | Deep Learning (book) | 89.0 | 1 |
| Books | Python Crash Course (book) | 45.0 | 2 |
| Books | SQL Pocket Guide | 25.0 | 3 |
| Electronics | 27-inch monitor | 329.0 | 1 |
| Electronics | Noise-cancelling headphones | 249.0 | 2 |
| Electronics | Mechanical keyboard | 119.0 | 3 |
| Electronics | USB-C hub | 49.0 | 4 |
| Electronics | Wireless mouse | 29.0 | 5 |
| Furniture | Standing desk | 549.0 | 1 |
| Furniture | Ergonomic chair | 399.0 | 2 |
| Furniture | Desk lamp | 59.0 | 3 |
| Grocery | Coffee beans 1 kg | 32.0 | 1 |
| Grocery | Green tea, 100 bags | 12.0 | 2 |
| Stationery | Gel pens, pack of 10 | 11.0 | 1 |
| Stationery | A5 notebook | 8.5 | 2 |
15 rows
Related
Learn it step by step in the free lesson CTEs & Window Functions, with exercises that check your queries.