Problem Statement:
We have a table 'Student' as shown below. And we wanted to find the rank of the students in the class.Student_Name Total_Marks
John 980
Peter 990
Vijay 990
Raj 960
Patrick 980
Arnold 950
William 955
gilbert 955
Solution:
The first thing that would strike is, If we sort the data in descending order, we would get the result. But from a practical point of view Ranking is not just about ordering but also resolve the conflict of those students whose rank is the same because both of them got the same marks. And what about the next student's rank whose total marks comes next to these conflicting students. To answer this we write a smart query for finding Rank. So lets see that,
Select S1.Student_Name, S1.Total_Marks,Count(S2.Total_Marks) as Rank
From Student S1, Student S2
where S1.Total_marks <= S2.Total_Marks
or (S1.Total_Marks = S2.Total_Marks and S1.Student_Name = S2.Student_Name)
Group by S1.Student_Name, S1.Total_Marks
Order by S1.Total_Marks DESC , S1.Name
Result:
Student_Name Total_Marks Rank
Peter 990 1
Vijay 990 1
John 980 3
Patrick 980 3
Raj 960 5
gilbert 955 6
William 955 6
Arnold 950 8
Sounds Good right.
-
KVSK
