How to handle bindings when ranking results in MySQL?

Question

How to handle relationships when ranking results in mysql query? I simplified the table names and columns in this example, but this 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 that 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 

Despite the fact that Al and Amy have the same class, one of them is higher than the other. Amy was ripped off. How can I make Amy and Al have the same ranking, so they both have rank 1. In addition, William and Mary did not pass the exam. They packed the class and smoked in the boys room. They should be tied to last place.

The correct rating 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.

Answer 1

EDIT : MySQL 4.1+ supported

Using:

  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 cross-connect (in MySQL, INNER JOIN without any criteria) to declare and use a variable without using a separate SET statement.

Correct NULL processing requires COALESCE.

Answer 2

It usually sounds like middleware that is better expressed in code that sits between the database and the client.

If this is not possible, I would recommend a stored procedure in MySQL to run the query when you wrote it, and then modify the results with the cursor and array.