Difference between DENSE_RANK and ROW_NUMBER function with a case study

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.