MySQL DENSE_RANK Function

The DENSE_RANK() function in MySQL is used to assign a rank to each row within a partition of a result set. Unlike RANK(), it does not leave gaps in ranking when there are duplicate values.

1. What is DENSE_RANK in MySQL?

✅ Assigns a ranking to rows based on a specified order
✅ No gaps between ranks, even for duplicate values
✅ Similar to RANK(), but more compact

🔹 Syntax:

DENSE_RANK() OVER (PARTITION BY column ORDER BY column)

2. Key Differences Between RANK() and DENSE_RANK()

Function	Handles Duplicates	Gaps in Ranking?
`RANK()`	Same values get the same rank	✅ Yes (skips ranks)
`DENSE_RANK()`	Same values get the same rank	❌ No gaps

Example Difference

ID	Name	Score	`RANK()`	`DENSE_RANK()`
1	Alice	90	1	1
2	Bob	85	2	2
3	Charlie	85	2	2
4	David	80	4	3

⏩ DENSE_RANK() does not skip 3, unlike RANK(), which jumps from 2 → 4.

3. How to Use DENSE_RANK() in MySQL

Example: Ranking Students by Score

SELECT id, name, score,
       DENSE_RANK() OVER (ORDER BY score DESC) AS rank
FROM students;

🔹 Results:

ID	Name	Score	Rank
1	Alice	95	1
2	Bob	90	2
3	Charlie	90	2
4	David	85	3

4. Using DENSE_RANK() with PARTITION BY

You can group ranks by category using PARTITION BY.

Example: Ranking Employees by Department

SELECT id, name, department, salary,
       DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;

🔹 Results:

ID	Name	Department	Salary	Rank
1	Alice	IT	5000	1
2	Bob	IT	4800	2
3	Charlie	HR	4500	1
4	David	HR	4200	2

✅ Employees are ranked within their department.

5. When to Use DENSE_RANK()?

✅ When you don't want ranking gaps
✅ Useful for leaderboards, competitions, and report generation
✅ Ideal for ranking grouped data (e.g., sales by region)

6. Conclusion

DENSE_RANK() ranks rows without gaps.
It's useful for grouped rankings using PARTITION BY.
It differs from RANK(), which skips numbers when duplicates exist.

🚀 Use DENSE_RANK() for compact and sequential ranking in MySQL queries!

Souy Soeng

MySQL DENSE_RANK Function