EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials SQL Tutorial SQL DENSE_RANK()
 

SQL DENSE_RANK()

Ravi Khatri
Article byRavi Khatri
EDUCBA
Reviewed byRavi Rathore

Updated June 13, 2023

SQL DENSE_RANK()

 

 

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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:

SQL DENSE_RANK() 1

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:

SQL DENSE_RANK() 2

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 DESC

The result set is as follows:

find Nth highest salary 3

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 = 1

The result set is as follows:

SQL DENSE_RANK() 4

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 = 2

The result set is as follows:

SQL DENSE_RANK() 5

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:

PARTITION BY clause example 4

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:

example 4-1

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.

  1. MySQL Create Function
  2. Working of MySQL encode()
  3. MySQL EXISTS
  4. MySQL DELETE Trigger

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW