How to compute ranks in mysql?

Question

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?

Related: http://stackoverflow.com/questions/1895110/row-number-in-mysql — OMG Ponies, Jun 17 '12 at 14:48
If you look into the expected ranks column in above question, there is no rank 2. This is because there were two guys with rank 1. I was querying a way to get the ranks in above question(1,3,1,5,3) instead of (1,2,1,3,2). — sushil, Jun 17 '12 at 15:19
Do you have to do this in mysql? It's probably quicker and easier to do where you display it. — jpiasetz, Jun 17 '12 at 19:20
Yes, its much easier to where its getting displayed but I was looking for alternatives. — sushil, Jun 18 '12 at 05:08

score 3 · Accepted Answer · answered Jun 17 '12 at 19:57

3

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

answered Jun 17 '12 at 19:57

jpiasetz

1,692
4
21
35

This is dangerous to do in MySQL. See here: http://dev.mysql.com/doc/refman/5.0/en/user-variables.html - "As a general rule, you should never assign a value to a user variable and read the value within the same statement." – goat Jun 17 '12 at 20:36
Do I not avoid that with "To avoid problems with this behavior, either do not assign a value to and read the value of the same variable within a single statement, or else set the variable to 0, 0.0, or '' to define its type before you use it." – jpiasetz Jun 17 '12 at 20:51
They're referring to the type of results for a column in that quote. I was referring to their warning about assuming an execution order of the statements in a select clause. – goat Jun 17 '12 at 20:55

How to compute ranks in mysql?

1 Answers1