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.

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".

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.

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.

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

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.