7

How does one handle ties when ranking results in a mysql query? I've simplified the table names and columns in this example, but it should illustrate my problem:

SET @rank=0;

   SELECT student_names.students, 
          @rank := @rank +1 AS rank, 
          scores.grades
     FROM student_names  
LEFT JOIN scores ON student_names.students = scores.students
 ORDER BY scores.grades DESC

So imagine the the above query produces:

Students  Rank  Grades
=======================
Al         1     90
Amy        2     90
George     3     78
Bob        4     73
Mary       5     NULL
William    6     NULL

Even though Al and Amy have the same grade, one is ranked higher than the other. Amy got ripped-off. How can I make it so that Amy and Al have the same ranking, so that they both have a rank of 1. Also, William and Mary didn't take the test. They bagged class and were smoking in the boy's room. They should be tied for last place.

The correct ranking should be:

Students  Rank  Grades
========================
Al         1     90
Amy        1     90
George     2     78
Bob        3     73
Mary       4     NULL
William    4     NULL

If anyone has any advice, please let me know.

Laxmidi
  • 2,650
  • 12
  • 49
  • 81

2 Answers2

16

EDIT: This is MySQL 4.1+ supported

Use:

   SELECT st.name,
          sc.grades,
          CASE 
            WHEN @grade = COALESCE(sc.grades, 0) THEN @rownum 
            ELSE @rownum := @rownum + 1 
          END AS rank,
          @grade := COALESCE(sc.grades, 0)
     FROM STUDENTS st
LEFT JOIN SCORES sc ON sc.student_id = st.id
     JOIN (SELECT @rownum := 0, @grade := NULL) r
 ORDER BY sc.grades DESC

You can use a cross join (in MySQL, an INNER JOIN without any criteria) to declare and use a variable without using a separate SET statement.

You need the COALESCE to properly handle the NULLs.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • Hi OMG Ponies, You are the MySQL Terminator!! Awesome, thanks so much for the help. I never would have figured it out. (My SQL knowledge is basic, so I'll have to study your answer in order to understand it better). Also, I love your screen name. Again, THANKS!!! -Laxmidi – Laxmidi Mar 19 '10 at 02:23
  • "You can use a cross join (in MySQL, an INNER JOIN without any criteria) to declare and use a variable without using a separate SET statement." Damn, I had no idea you could do this. +1 – heisenberg Mar 19 '10 at 03:11
  • OMG Ponies - I tried out your solution it worked perfectly. But I must confess I'm still not entirely sure how it works. Can you add some more detail on how it get students with the same grade to have the same score? Or if there is a link elsewhere which describes this technique. Thanks! – Brian Armstrong Apr 12 '11 at 00:53
  • @Brian Armstrong: The CASE statement will repeat the rownum value when the grade variable equals either the current row, or zero. Otherwise, the rank value will increase & be displayed for the row. The last column in the SELECT clause sets the grade value to the variable for comparing to the next grade value. The ORDER BY ensures that grades with the highest rank will have the lowest number. – OMG Ponies Apr 12 '11 at 04:11
1

Sounds like a middleware rule that would be better expressed in code that sat between the database and the client.

If that's not possible, I'd recommend a stored procedure in MySQL to run the query as you wrote it and then modify the results using a cursor and an array.

duffymo
  • 305,152
  • 44
  • 369
  • 561