RANK and DENSE_RANK
- SQL course
- Advanced
- Lesson 15/81
- 4 min
Learn the ranking functions.
RANK and DENSE_RANK
RANK skips on ties, DENSE_RANK does not. Both give rankings.
SELECT name, price,
RANK() OVER (ORDER BY price DESC) AS rk,
DENSE_RANK() OVER (ORDER BY price DESC) AS dr
FROM products;This query gives two kinds of ranking.
name, price, RANK() OVER (ORDER BY price DESC) AS rk, … is selected from products. RANK() is a window function: it computes a value for each row over the window defined in OVER (…), without collapsing rows.
RANK and DENSE_RANK — details
If two rows tie at 1, RANK skips to 3, DENSE_RANK continues at 2.
In competition results, use RANK if you want "two people first, the next one third", and DENSE_RANK if you want "the next one second".
Practice RANK and DENSE_RANK
Compute both RANK() and DENSE_RANK() for each product.
Starter code
-- two rankings
SELECT name, price,
RANK() OVER (ORDER BY price DESC) AS rkSample solution
SELECT name, price,
RANK() OVER (ORDER BY price DESC) AS rk,
DENSE_RANK() OVER (ORDER BY price DESC) AS dr
FROM products;Expected output
name rk dr Laptop 1 1 Telefon 2 2
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 RANK and DENSE_RANK: match the terms
| Term | Meaning |
|---|---|
RANK |
skips |
DENSE_RANK |
no skip |
ties |
same value |
ranking |
rank |
2 Add DENSE_RANK
SELECT name, price,
RANK() OVER (ORDER BY price DESC) AS rk,
____() OVER (ORDER BY price DESC) AS dr
FROM products;SELECT name, price,
RANK() OVER (ORDER BY price DESC) AS rk,
DENSE_RANK() OVER (ORDER BY price DESC) AS dr
FROM products;Explanation: DENSE_RANK is the right answer.
3 What does DENSE_RANK do on ties?
- Skips
- An error
- Does not skip Correct answer
- Resets
Explanation: DENSE_RANK does not skip on ties.