The DENSE_RANK() function in SQL is used to assign a rank to each row within a result set partitioned by a specified column and ordered by a specified criterion. It assigns ranks in such a way that there are no gaps in the ranking sequence when there are ties (i.e., rows with the same values in the specified order).
Here’s how to use the DENSE_RANK() function step-by-step:
1. Basic Syntax:
The basic syntax for using the DENSE_RANK() function is as follows:
DENSE_RANK() OVER (PARTITION BY column1, column2 ORDER BY column3)
2. Components:
- PARTITION BY: This clause divides the result set into partitions to which the DENSE_RANK() function is applied. It’s optional; if not specified, the function treats the entire result set as a single partition.
- ORDER BY: This clause determines the order of the rows within each partition. It is required.
3. Example:
Assume we have a table called Employees with the following structure:
CREATE TABLE Employees (
EmployeeID INT,
DepartmentID INT,
Salary DECIMAL(10, 2)
);
If we want to assign a dense rank to each employee within each department based on their salary, we can write the following query:
SELECT
EmployeeID,
DepartmentID,
Salary,
DENSE_RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS DenseRank
FROM
Employees
ORDER BY
DepartmentID, DenseRank;
4. Explanation of the Example:
- This query selects the EmployeeID, DepartmentID, and Salary from the Employees table.
- It computes a dense rank (DenseRank) based on the Salary, ordered in descending order, but resets for each DepartmentID.
- The result set will be ordered by DepartmentID first, then by the dense rank.
5. Output:
The output will look like this (assuming the relevant data exists):
| EmployeeID | DepartmentID | Salary | DenseRank |
|------------|--------------|---------|-----------|
| 101 | 1 | 70000.00| 1 |
| 102 | 1 | 50000.00| 2 |
| 103 | 1 | 50000.00| 2 |
| 104 | 2 | 60000.00| 1 |
| 105 | 2 | 40000.00| 2 |
In this output, you can see that for DepartmentID 1, both employee 102 and 103 have the same salary, and therefore they receive the same dense rank (2), but there is no gap as rank 1 is assigned to the highest salary. For DepartmentID 2, rank 1 is given to the highest salary (employee 104), and rank 2 is assigned to employee 105 with a lower salary.