Difference between DENSE_RANK and ROW_NUMBER function with a case study

Rajnilari2015
Posted by in Sql Server category on for Beginner level | Points: 250 | Views : 992 red flag

In many cases, developers get confuse between the difference of DENSE_RANK and ROW_NUMBER function.These both are ranking function being introduce in SQL Server 2005.In this article we will look into the difference between these two function by using a case study.


 Download source code for Difference between DENSE_RANK and ROW_NUMBER function with a case study

Recommendation
Read Extract numbers from string using T-SQL before this article.

Introduction

In many cases, developers get confuse between the difference of DENSE_RANK and ROW_NUMBER function.These both are ranking function being introduce in SQL Server 2005. In this article we will look into the difference between these two function by using a case study "Find the N-th highest marks obtained by Students".

Environment Setup

To demonstrate the point, let's create a table and insert some values

DECLARE @T TABLE(RollNumber INT,Name VARCHAR(20),Subject VARCHAR(20),Marks INT)
INSERT INTO @T
SELECT 1,'Name1','Maths', 77  UNION ALL
SELECT 1,'Name1', 'Biology', 44 UNION ALL
SELECT 1,'Name1', 'Chemistry', 54 UNION ALL
SELECT 1,'Name1', 'English', 61 UNION ALL
SELECT 2,'Name2','Maths', 55  UNION ALL
SELECT 2,'Name2', 'Biology', 67 UNION ALL
SELECT 2,'Name2', 'Chemistry', 91 UNION ALL
SELECT 2,'Name2', 'English', 45 UNION ALL
SELECT 3,'Name3','Maths', 66  UNION ALL
SELECT 3,'Name3', 'Biology', 88 UNION ALL
SELECT 3,'Name3', 'Chemistry', 68 UNION ALL
SELECT 3,'Name3', 'English', 73 UNION ALL
SELECT 4,'Name4','Maths', 66  UNION ALL
SELECT 4,'Name4', 'Biology', 67 UNION ALL
SELECT 4,'Name4', 'Chemistry', 68 UNION ALL
SELECT 4,'Name4', 'English', 69 UNION ALL
SELECT 5,'Name5','Maths', 77  UNION ALL
SELECT 5,'Name5', 'Biology', 64 UNION ALL
SELECT 5,'Name5', 'Chemistry', 56 UNION ALL
SELECT 5,'Name5', 'English', 61 

SELECT *
FROM @T

Objective

As can be figure out that there are altogether 4 subjects viz Maths,Biology,Chemistry,English.And the number of students are 5.The objective is to find out the Nth highest marks obtained by students in the subjects by demonstrating the appropriate usage of DENSE_RANK and ROW_NUMBER function .Also we need to handle the case if there is a tie situation.

Using the code

Now, let us execute the below query

DECLARE @RankByMarks INT = 3

SELECT 
	X.RollNumber
	,X.Name
	,X.Subject
	,X.Marks
	,X.Rn
FROM ( SELECT
        t.*
        ,Rn = DENSE_RANK() OVER(PARTITION BY t.Subject ORDER BY t.Marks DESC)
FROM @T t) X
ORDER BY 1

Explanation

In the example, we are finding out the third highest marks that the students has obtained.The usage of DENSE_RANK() along with the PARTITION BY made over "subject" column ensures the proper usage of the ranking made within the subjects.It also makes sure that if there is a tie situation then that will be handle. We can figure out that for the subject "English" both RollNumber 1 and 5 has obtained the same marks which is 61.

The final query will be

DECLARE @RankByMarks INT = 3

SELECT 
	X.RollNumber
	,X.Name
	,X.Subject
	,X.Marks	
FROM ( SELECT
        t.*
        ,Rn = DENSE_RANK() OVER(PARTITION BY t.Subject ORDER BY t.Marks DESC)
FROM @T t) X WHERE X.Rn = @RankByMarks
ORDER BY 1

One may argue that, we can achieve the same by using the ROW_NUMBER() function but that is not true.

DECLARE @RankByMarks INT = 3

SELECT 
	X.RollNumber
	,X.Name
	,X.Subject
	,X.Marks
	,X.Rn	
FROM ( SELECT
        t.*
        ,Rn = ROW_NUMBER() OVER(PARTITION BY t.Subject ORDER BY t.Marks DESC)
FROM @T t) X --WHERE X.Rn = @RankByMarks
ORDER BY 1

As can be figure out that, the ROW_NUMBER() along with the PARTITION BY made over "subject" column provides rank among itself and does not handle the tie.Henceforth, it will be a problem if we issue the ROW_NUMBER() ranking function here instead of DENSE_RANK()

The output using ROW_NUMBER()

Reference

ROW_NUMBER

DENSE_RANK

Conclusion

This article has given a distinction between the usage of the two ranking function DENSE_RANK and ROW_NUMBER by using a suitable case study.Hope this will be useful.Thanks for reading.Zipped file is attached herewith.

Page copy protected against web site content infringement by Copyscape

About the Author

Rajnilari2015
Full Name: Niladri Biswas (RNA Team)
Member Level: Platinum
Member Status: Member,MVP
Member Since: 3/17/2015 2:41:06 AM
Country: India
-- Thanks & Regards, RNA Team


Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)