What is the difference between RANK and DENSE_RANK in SQL?
In SQL, both RANK and DENSE_RANK are used to assign a ranking value to each row within a partition of a result set based on specified criteria, often using the ORDER BY clause. The main difference between the two lies in how they handle ties, which occur when two or more rows have the same value in the ranking order. RANK assigns a unique rank number to each distinct value, which means that if there are ties, the subsequent rank numbers will skip accordingly. In contrast, DENSE_RANK assigns ranks without gaps; if there are ties, the next rank number in sequence will be the immediate next integer.
For example, consider a simple dataset of employee salaries:
| Employee | Salary |
|---|---|
| Alice | 5000 |
| Bob | 5000 |
| Charlie | 6000 |
| David | 7000 |
When applying RANK, the query might look like this:
SELECT Employee, Salary, RANK() OVER (ORDER BY Salary DESC) AS Rank
FROM Employees;
The result would be:
| Employee | Salary | Rank |
|---|---|---|
| David | 7000 | 1 |
| Charlie | 6000 | 2 |
| Alice | 5000 | 3 |
| Bob | 5000 | 3 |
Here, Alice and Bob both earn the same salary and are ranked 3. The next rank available would be 4, which David jumps over, skipping an integer.
Now considering DENSE_RANK applied to the same data:
SELECT Employee, Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank
FROM Employees;
The result will be:
| Employee | Salary | DenseRank |
|---|---|---|
| David | 7000 | 1 |
| Charlie | 6000 | 2 |
| Alice | 5000 | 3 |
| Bob | 5000 | 3 |
In this case, Alice and Bob still share the same rank, but the next rank, which follows their tied rank, is 3 instead of jumping to 4. This difference is crucial in understanding how ranking works in SQL and can have implications for reporting and data analysis where precise ranking order is important.