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

Try searching for dense rank in mysql. It is there in oracle so should have something similar in mysql too — Utsav, Sep 08 '15 at 00:46
@Utsav, I think the answer (http://stackoverflow.com/questions/3333665/rank-function-in-mysql) does not handle duplicate score issue. I tried it is not working for me. I will update post, and good advice is appreciated. — Lin Ma, Sep 08 '15 at 00:54
Is there a way to first the do the rank checking before doing the scoring checking first? — Ryan Fung, Sep 08 '15 at 02:09
@RyanFung, thanks for the advice, not quite catch you, if you could write your ideas in SQL code, it will be great. :) — Lin Ma, Sep 08 '15 at 02:16

score 2 · Answer 1 · answered Sep 08 '15 at 02:04

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;

answered Sep 08 '15 at 02:04

Gordon Linoff

1,242,037
58
646
786

@Gorden Linoff, dumb question, why using Cross join? :) – Lin Ma Sep 08 '15 at 02:12
1

@LinMa . . . As opposed to what? I *NEVER* use commas in the `FROM` clause, except in MS Access which does not have `CROSS JOIN`. – Gordon Linoff Sep 08 '15 at 18:47

score 2 · Accepted Answer · answered Sep 08 '15 at 04:10

2

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.

answered Sep 08 '15 at 04:10

Mark Madej

1,752
1
14
19

smart and smart. Wondering why you are using left join, other than inner join? – Lin Ma Sep 08 '15 at 04:42
1

You have to use a left join or the inner query will not return any rows for the score = 4 case. No rows will be returned because there is no score higher than the highest score. Kinda confusing but hopefully you get what I'm saying. :) – Mark Madej Sep 08 '15 at 04:44
one more question, you are using "LEFT JOIN scores score_higher ON score_higher.score > score_primary.score GROUP BY score_primary.score", how to know if (1) first execute group by on original table, then left join on group by results, (2) first left join, then group on results of left join. I think you are executing the latter, but want to know why and how. – Lin Ma Sep 08 '15 at 07:30
BTW, @Mark, met with a new issue after more exercise. Your advice is appreciated, here is the issue => http://stackoverflow.com/questions/32451858/why-this-left-join-returns-only-one-record – Lin Ma Sep 08 '15 at 07:37

calculating ranking in SQL

2 Answers2