calculating ranking in SQL

Question

I have a table which has float score, and I want to rank them from largest to smallest, if the same score, same ranking. I am using MySQL/MySQL Workbench, and any good ideas are appreciated.

Here is a sample input and output,

+----+-------+
| Id | Score |
+----+-------+
| 1  | 3.50  |
| 2  | 3.65  |
| 3  | 4.00  |
| 4  | 3.85  |
| 5  | 4.00  |
| 6  | 3.65  |
+----+-------+

+-------+------+
| Score | Rank |
+-------+------+
| 4.00  | 1    |
| 4.00  | 1    |
| 3.85  | 2    |
| 3.65  | 3    |
| 3.65  | 3    |
| 3.50  | 4    |
+-------+------+

Tried the following query, but not working since it does not handle duplicate,

SELECT    id, score,
          @curRank := @curRank + 1 AS rank
FROM      TestRank tr, (SELECT @curRank := 0) r
ORDER BY  score desc;

In this above query, user 3 and user 5 have the same score value 4, but ranked differently.

I also tried the following query to just rank score itself, and it returns very weird results,

set @curRank := 0;
SELECT    distinct score, @curRank := @curRank+1 as rank
FROM      TestRank tr
ORDER BY  score desc;

thanks in advance, Lin

Answer 1

Check out this fiddle : http://sqlfiddle.com/#!9/17a49/3

Here's the query that will work for you:

SELECT
s.score, scores_and_ranks.rank
FROM
scores s
JOIN
( 
  SELECT
  score_primary.score, COUNT(DISTINCT score_higher.score) + 1 AS rank
  FROM 
    scores score_primary
  LEFT JOIN scores score_higher ON score_higher.score > score_primary.score
  GROUP BY score_primary.score
 ) scores_and_ranks
 ON s.score = scores_and_ranks.score
 ORDER BY rank ASC

In the "scores_and_ranks" inner query, we total up the number of distinct scores that are better than the current score. The top score will have zero, so we add 1 to get the rank value you want.

The reason we have to join to that table (using table "s") is to make sure the duplicate score values (two rows with score=4, for example) are shown in distinct rows.

Answer 2

You can do this by "remembering" the previous score:

SELECT id, score,
       (@curRank := if(@s = score, @curRank + 1,
                       if(@s := score, 1, 1)
                      )
       ) as rank
FROM TestRank tr CROSS JOIN
     (SELECT @curRank := 0, @s := -1) r
ORDER BY score desc;