RANK() and DENSE_RANK()
Learn how to use RANK() and DENSE_RANK() Window Functions in SQL Server to assign rankings to rows.
What are RANK() and DENSE_RANK()?
RANK() and DENSE_RANK() are SQL Server Window Functions used to assign rankings to rows based on a specified order.
Both functions assign the same rank to rows when they contain the same value.
The main difference is how they handle the next rank when duplicate values are present.
RANK()
The RANK() function assigns the same rank to rows having equal values.
When duplicate values occur, the next rank is skipped.
SELECT
EmployeeId,
EmployeeName,
Salary,
RANK() OVER
(
ORDER BY Salary DESC
) AS SalaryRank
FROM Employees;
RANK() Example
Suppose the employee salaries are:
| EmployeeName | Salary | RANK() |
|---|---|---|
| Rahul | 80000 | 1 |
| Amit | 70000 | 2 |
| Priya | 70000 | 2 |
| Neha | 60000 | 4 |
Amit and Priya both have a salary of 70000, so both receive rank 2. The next rank becomes 4, because rank 3 is skipped.
DENSE_RANK()
The DENSE_RANK() function also assigns the same rank to rows having equal values.
However, unlike RANK(), it does not skip the next ranking number after duplicate values.
SELECT
EmployeeId,
EmployeeName,
Salary,
DENSE_RANK() OVER
(
ORDER BY Salary DESC
) AS SalaryRank
FROM Employees;
DENSE_RANK() Example
| EmployeeName | Salary | DENSE_RANK() |
|---|---|---|
| Rahul | 80000 | 1 |
| Amit | 70000 | 2 |
| Priya | 70000 | 2 |
| Neha | 60000 | 3 |
Amit and Priya both receive rank 2, but Neha receives rank 3. No rank is skipped.
Difference Between RANK() and DENSE_RANK()
| Feature | RANK() | DENSE_RANK() |
|---|---|---|
| Same values get same rank | Yes | Yes |
| Skips rank after duplicate | Yes | No |
| Example | 1, 2, 2, 4 | 1, 2, 2, 3 |
| Useful for ranking | Yes | Yes |
Easy Way to Remember
Values:
100
90
90
80
RANK():
100 → 1
90 → 2
90 → 2
80 → 4
DENSE_RANK():
100 → 1
90 → 2
90 → 2
80 → 3
RANK() with PARTITION BY
We can use PARTITION BY with RANK() to create separate rankings for different groups.
SELECT
EmployeeId,
EmployeeName,
Department,
Salary,
RANK() OVER
(
PARTITION BY Department
ORDER BY Salary DESC
) AS DepartmentRank
FROM Employees;
In this example, employees are ranked separately within each department.
DENSE_RANK() with PARTITION BY
DENSE_RANK() can also be used with PARTITION BY.
SELECT
EmployeeId,
EmployeeName,
Department,
Salary,
DENSE_RANK() OVER
(
PARTITION BY Department
ORDER BY Salary DESC
) AS DepartmentRank
FROM Employees;
The ranking starts again for every department, without leaving gaps between ranking numbers.
Practical Uses
- Ranking employees based on salary.
- Ranking students based on marks.
- Ranking products based on sales.
- Finding top-performing employees.
- Creating department-wise rankings.
- Comparing records based on a particular value.
Important Points
- Both RANK() and DENSE_RANK() use the OVER() clause.
- Equal values receive the same rank.
- RANK() skips ranking numbers after duplicates.
- DENSE_RANK() does not skip ranking numbers.
- PARTITION BY can create separate rankings for different groups.
Summary
RANK() and DENSE_RANK() are Window Functions used to assign rankings to rows.
RANK() leaves gaps after duplicate rankings, while DENSE_RANK() continues with the next consecutive ranking. Both functions can be combined with PARTITION BY to calculate rankings separately for different groups.