ROW_NUMBER / RANK / DENSE_RANK
Overview
Ranking functions assign a sequential integer to rows within a partition. ROW_NUMBER assigns a unique integer (1, 2, 3). RANK assigns the same number for ties but skips subsequent numbers (1, 1, 3). DENSE_RANK assigns the same number for ties and does not skip numbers (1, 1, 2). Think of a race: RANK is your finishing place (if two people tie for 1st, the next person arrived 3rd). DENSE_RANK is the medal tier (Gold, Gold, Silver - no tiers are skipped).
These functions are essential for returning 'top N' results per group, finding the latest record per customer, or building leaderboards.
Where used: leaderboards, getting the latest order per customer
Why learn this
- You can easily deduplicate rows by keeping only the row where ROW_NUMBER() = 1.
- You can build leaderboards that handle ties gracefully.
Query walkthrough
SELECT product_id, quantity, RANK() OVER (ORDER BY quantity DESC) as rnk, DENSE_RANK() OVER (ORDER BY quantity DESC) as drnk FROM order_items WHERE order_id IN (1, 4) ORDER BY quantity DESC, product_id;Focus: Observe how RANK skips the value 2 after a tie, while DENSE_RANK assigns 2 immediately after.
Common mistakes
- Using RANK when you want unique numbers: If two rows have the same value in the ORDER BY clause, RANK() gives them the same rank. Use ROW_NUMBER() if you strictly need unique sequence numbers.
Glossary
- sequential
- Following in a logical order or sequence, such as numbers counting up.
- deduplicate
- To find and remove exact copies of data, keeping only one unique version of each record.
- leaderboards
- Ranked lists showing the top performers or items, often used in games, sales, or data analytics.
Recall questions
- What happens if there is a tie when using DENSE_RANK()?
Understanding checks
What rows does this query return?
4 | 149.99 | 1 5 | 89.99 | 2
There are two furniture items. Desk (id=4) is 149.99, Chair (id=5) is 89.99. ROW_NUMBER assigns 1 and 2 sequentially based on descending price.
What rows does this query return?
1 | 2023-02-01 | 1 2 | 2023-03-15 | 2 4 | 2023-04-20 | 3
It filters for paid orders, sorts by order_date, and assigns a sequential number. Then limits to the first 3 rows.
Practice tasks
Add a row number to employees
Given this query, add a ROW_NUMBER() column named `salary_rank` that assigns 1 to the highest paid employee, 2 to the next, etc.
Challenge
Find the highest paid employee in each department
Write a query returning name, department, salary, and their RANK based on salary descending within their department. Order by department, then rank.
Continue learning
Previous: OVER & PARTITION BY