The DENSE_RANK() function in SQL is used to assign a unique rank to each distinct value in a partition of a result set. Unlike the RANK() function, which can skip ranks when there are ties (i.e., when two rows have the same value), DENSE_RANK() does not leave gaps between the ranks.
Here's the basic syntax for using DENSE_RANK():
DENSE_RANK() OVER ( [PARTITION BY partition_expression] ORDER BY sort_expression)
Parameters:
- PARTITION BY: This clause divides the result set into partitions to which the DENSE_RANK() function is applied. If you omit this clause, the entire result set is treated as a single partition.
- ORDER BY: This clause defines the order of the rows within each partition to assign the rank.
Example Usage:
Let’s assume we have a table named Employees with the following structure:
| EmployeeID | Name | Department | Salary |
|------------|-----------|------------|--------|
| 1 | Alice | HR | 70000 |
| 2 | Bob | HR | 80000 |
| 3 | Charlie | IT | 60000 |
| 4 | David | IT | 60000 |
| 5 | Eva | Sales | 90000 |
We want to rank the employees based on their salary within each department. Here’s how you can do it using DENSE_RANK():
SELECT
EmployeeID,
Name,
Department,
Salary,
DENSE_RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS SalaryRank
FROM
Employees;
Result:
| EmployeeID | Name | Department | Salary | SalaryRank |
|------------|---------|------------|--------|------------|
| 2 | Bob | HR | 80000 | 1 |
| 1 | Alice | HR | 70000 | 2 |
| 4 | David | IT | 60000 | 1 |
| 3 | Charlie | IT | 60000 | 1 |
| 5 | Eva | Sales | 90000 | 1 |
Key Points:
- In the HR department, Bob is ranked 1, and Alice is ranked 2 because there is no tie.
- In the IT department, both David and Charlie earn the same salary, so they both receive a DENSE_RANK() of 1, but the next distinct rank would be 1 again (there's no gap).
- In the Sales department, Eva ranks 1 since she’s the only employee in that department.
Using DENSE_RANK() is helpful when you need to rank items and want to handle ties without leaving gaps in the rank values.