If you’ve ever needed to rank rows in SQL queries while ensuring no gaps in ranking numbers, then DENSE_RANK() is your friend. This analytical function is incredibly useful when you need to assign rankings based on specific sorting criteria, and unlike RANK(), it avoids skipping numbers when there are duplicates.
Understanding DENSE_RANK in SQL
The DENSE_RANK() function assigns a ranking to each row within a result set based on a specified column order. The key feature of this function is that it does not skip numbers when duplicate values exist.
Its syntax looks like this:
DENSE_RANK() OVER (
PARTITION BY column_name
ORDER BY column_name
)
Here’s how it works:
- PARTITION BY: Divides the data into partitions (optional).
- ORDER BY: Specifies the sorting order used for ranking.
DENSE_RANK vs RANK vs ROW_NUMBER
To understand DENSE_RANK() better, let’s compare it with RANK() and ROW_NUMBER() using the following dataset.
CREATE TABLE Sales (
ID INT PRIMARY KEY,
Employee VARCHAR(50),
Revenue INT
);
INSERT INTO Sales (ID, Employee, Revenue) VALUES
(1, 'Alice', 5000),
(2, 'Bob', 4000),
(3, 'Charlie', 5000),
(4, 'David', 3000),
(5, 'Eve', 3000);
Now, let’s apply all three functions:
SELECT
Employee, Revenue,
RANK() OVER (ORDER BY Revenue DESC) AS rank_value,
DENSE_RANK() OVER (ORDER BY Revenue DESC) AS dense_rank_value,
ROW_NUMBER() OVER (ORDER BY Revenue DESC) AS row_number_value
FROM Sales;
The result set would look like this:
| Employee | Revenue | RANK() | DENSE_RANK() | ROW_NUMBER() |
|---|---|---|---|---|
| Alice | 5000 | 1 | 1 | 1 |
| Charlie | 5000 | 1 | 1 | 2 |
| Bob | 4000 | 3 | 2 | 3 |
| David | 3000 | 4 | 3 | 4 |
| Eve | 3000 | 4 | 3 | 5 |
Key observations:
RANK()skips numbers when duplicates appear (notice the jump from 1 to 3 and 3 to 4).DENSE_RANK()does not skip numbers, keeping rankings compact.ROW_NUMBER()assigns unique row numbers without considering duplicate values.
Practical Use Cases of DENSE_RANK
Finding Top N Records within Groups
Say we have sales data grouped by departments, and we want to get the top 2 employees in each department based on revenue.
SELECT *
FROM (
SELECT Employee, Department, Revenue,
DENSE_RANK() OVER (PARTITION BY Department ORDER BY Revenue DESC) AS ranking
FROM Sales
) ranked
WHERE ranking <= 2;
Removing Duplicate Rankings
If your dataset contains duplicate values in a ranking field and you need a compact ranking list, DENSE_RANK() ensures the numbers are continuous instead of skipping.
Conclusion
DENSE_RANK() is a powerful SQL function when working with ranking data where you need dense, gap-free ranking values. Unlike RANK(), it does not skip numbers when duplicates exist, making it particularly useful for leaderboard statistics, sales analysis, and top-N reporting.
Understanding when to use DENSE_RANK() versus RANK() and ROW_NUMBER() can significantly improve how you structure your queries and manage result sets. Hopefully, the examples provided give you a clear insight into how it works in practice!
Other interesting article:
How RANK works in SQL? Best RANK examples