Updated June 13, 2023
Introduction to SQL DENSE_RANK()
DENSE_RANK() was introduced in SQL Server 2005 and it returns a rank that starts at 1 based on the ordering of the row and there is no gap in ranking values. So DENSE_RANK() returns a rank of the specific row, which is one plus distinct rank values that have come before the specific row. The DENSE_RANK() is a window function that assigns the rank to rows based on the current partition in the result. If the value is the same for two rows, then they will receive the same rank, which is similar to RANK(), which means if there are two rows at rank one and there are a total of four such rows, then DENSE_RANK() will return 1,1,2,3 whereas RANK() would return 1,1,3,4.
The following shows the common syntax of a DENSE_RANK() in SQL Server:
DENSE_RANK() OVER (
[PARTITION BY partition_expression, ... ]
ORDER BY sort_expression [ASC | DESC], ...
)The OVER clause in a window defines the partitioning and ordering of rows, making them window functions. We use the following arguments in this clause:
- ORDER BY – It represents the logical order of the rows.
- PARTITION BY divides the result into separate partitions, and the window function is applied individually to every partition.
The return type of this function is bigint. DENSE_RANK() function is a non-deterministic instead of a deterministic function, which returns the same result at any time. When called, this function may return a different result at a different time, even if the database state remains the same.
Examples of SQL DENSE_RANK()
Let us discuss examples of SQL DENSE_RANK().
Example #1
Creating table inserting rows and using DENSE_RANK()
CREATE TABLE dense_rank_tutorial(
alphabet VARCHAR(10)
);
INSERT INTO dense_rank_tutorial(alphabet) VALUES('A'),('B'),('B'),('C'),('C'),('D'),('E');
SELECT alphabet,
DENSE_RANK() OVER (ORDER BY alphabet) my_dense_rank
FROM dense_rank_tutorial;The result set is as follows:
We created a table, inserted values, and utilized the DENSE_RANK() function to determine the rank of the alphabets. The ranking was ordered alphabetically. As B and C appeared multiple times, they acquired the same rank. Since dense rank functions do not skip ranks, we obtained the distinct rank value preceding B or C and incremented it by 1 to obtain the ranks.
Example #2
Simple example with DENSE_RANK()
SELECT emp_name,emp_gender,emp_salary,
DENSE_RANK() OVER (ORDER BY emp_salary desc) AS DenseRank
FROM Employee;The result set is as follows:
This is the basic example in which we select name, gender, and salary from the Employee table and use DENSE_RANKfunction to get each employee’s salary rank. As you can see, the average of Tom and Ron each has 9500so, the rank is 1. Subsequently, the ranks are calculated for the rest of the entries. Notice that Jodi has a rank of 2 and not 3 since we have used a dense rank. If we had used the normal rank function, it would have skipped rank two and given rank 3 for Jodi.
Example #3
Using DENSE_RANK() to find Nth highest salary
SELECT * FROM Employee ORDER BY emp_salary DESCThe result set is as follows:
This gives the salary of all employees in descending order
WITH SalaryResult AS
(
SELECT emp_gender,emp_salary,emp_name,
DENSE_RANK() OVER (ORDER BY emp_salary desc) AS SalaryDenseRank
FROM Employee
)
SELECT TOP 1 emp_salary,emp_name FROM SalaryResult WHERE SalaryDenseRank = 1The result set is as follows:
Here we have used DENSE_RANK() to calculate each employee’s salary rank. But we have wrapped it in a CTE(Common Table Expression) SalaryResult to use in the following query.
The following shows the common syntax of a CTE in SQL Server if you are not familiar with it:
WITH expression_name[(column_name [,...])]
AS
(CTE_definition)
SQL_statement;In the subsequent query, we utilize the SalaryResult CTE and retrieve the salary and name of the employee whose dense rank is 1.
WITH SalaryResult AS
(
SELECT emp_gender,emp_salary,emp_name,
DENSE_RANK() OVER (ORDER BY emp_salary desc) AS SalaryDenseRank
FROM Employee
)
SELECT TOP 1 emp_salary,emp_name FROM SalaryResult WHERE SalaryDenseRank = 2The result set is as follows:
In this example we have written a similar query, but in this case, we have used second rank and got the result using DENSE_RANK(). Instead, if we have used RANK(), the result set will be empty as there is no second rank. As we can see in the entire result set, TOM and RON share the same salary.
Example #4
Using DENSE_RANK() with PARTITION BY clause
SELECT emp_gender,emp_salary,emp_name,
DENSE_RANK() OVER (PARTITION BY emp_gender ORDER BY emp_salary desc) AS SalaryDenseRank
FROM Employee;The result set is as follows:
In this, we have used DENSE_RANK() and partitioned the data using gender, so now this function work within the window of the partition. Now we can use this to our advantage in the following query.
WITH SalaryResult AS
(
SELECT emp_gender,emp_salary,emp_name,
DENSE_RANK() OVER (PARTITION BY emp_gender ORDER BY emp_salary desc) AS SalaryDenseRank
FROM Employee
)
SELECT TOP 1 emp_salary,emp_name,emp_gender FROM SalaryResult WHERE SalaryDenseRank= 3 and emp_gender ='Female'The result set is as follows:
In this query, we got the rankings from DENSE_RANK(), and using SQL CTE, we got the result of a female employee with dense rank 3, and her name is Sara. To achieve this, we partition the data based on gender, allowing us to obtain separate rankings for male and female employees.
Conclusion
Now you have a clear understanding of what DENSE_RANK() is in the SQL server and how it is utilized to obtain ranks from the values specified in a given column.
Recommended Articles
We hope that this EDUCBA information on “SQL DENSE_RANK()” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
