Know SQL Rank through SQL query.

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