How do I Handle Ties When Ranking Results in MySQL?

mysql, rank, sql, window-functions

Solution

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.

Problem

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.

Original source