SQL Server Ranking Functions

Similar to FOR XML statement, i want to share other concept “Ranking Function”. Normally this topic also will make confusion, if you are not understand properly. I tried to simplify the concept for better understanding. In School days, we were ranked based on our exam marks, 1st rank will be given to who are all got highest marks like wise in SQL Server,

1. RANK     2.DENSE_RANK         3.NTILE

The results of the above functions are easy to understand but when it comes with partition column, it might confuse us. The normal results of the above functions are shown below

SELECT StudentCode, subjectcode,Marks AS Marks,

RANK () OVER (ORDER BY Marks ASC) AS Rank, DENSE_RANK () OVER (ORDER BY MarksASC) AS DenseRank,

NTILE(2) OVER (ORDER BY Marks ASC) AS Ntil FROM

StudentsMarks

To Explain ranking functions with partition by clause, i will use scenarios.

Scenario

I want to show the students who scored the highest marks for each subject along with marks.

Solution:

Here i need to use subject, if subject is not specified in the scenario then i can go with simple Rank function and order by desc and filter Rank 1

SELECT StudentCode AS Code, SubjectCode,Marks AS Value FROM (

SELECT StudentCode, SubjectCode,Marks AS Marks,

RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks DESC) AS Rank

FROM StudentsMarks) tmp

WHERE Rank = 1

For better understanding i will run the subquery first and let see the result.

SELECT StudentCode, SubjectCode,Marks AS Marks,

RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks DESC) AS Rank

FROM StudentsMarks

Now the outer query will execute on above result. so as per the condition in WHERE, we will get 3 records as a output.

Please let me know for more scenarios. I will explain as per your needs.