CODEWITHSUNDEEP
mysql
sushil
sushil

Reputation: 2671

How to compute ranks in mysql?

I have a table 'student_marks' with two columns 'student_id' and 'mark':

student_id | marks
-------------------
    1      |  5
    2      |  2
    3      |  5
    4      |  1
    5      |  2

I need to compute the rank corresponding to the marks. The expected output for the above table is:

student_id | marks  | rank
-------------------------
    1      |  5     | 1
    2      |  2     | 3
    3      |  5     | 1
    4      |  1     | 5
    5      |  2     | 3

Since the two students with students_id 1 and 3 has highest mark 5, they are placed in rank 1. For students with marks 2, the rank is 3 as there are two students who has more marks then these guys.

How do we write queries to compute the ranks as shown above?

Upvotes: 2

Views: 283

Answers (1)

jpiasetz
jpiasetz

Reputation: 1772

This should work although it's heavy on variables.

SELECT student_id, mark, rank FROM (
    SELECT t.*,
           @rownum := @rownum + 1 AS realRank,
           @oldRank := IF(mark = @previous,@oldRank,@rownum) AS rank,
           @previous := mark
    FROM student_marks t, 
         (SELECT @rownum := 0) r,
         (SELECT @previous := 100) g,
         (SELECT @oldRank := 0) h
    ORDER BY mark DESC
 ) as t
 ORDER BY student_id;

Look at this fiddle: http://sqlfiddle.com/#!2/2c7e5/32/0

Upvotes: 3

Related Questions