1

I have a very simple MySQL table where I save highscores. It looks like that:

Id Name Score SubmitDate So far so good. The question is: How do I get what's a users rank EVEN THEIR SCORE IS TIE, I want them have diff ranking base on the submission date (older submission date have higher ranking)? For example, I have a users Name or Id and want to get his rank.

An Example

Id  Name    Score    SubmitDate
1   Ida     100      2014-08-01 21:37:17
2   Boo     99       2014-08-02 21:37:17
3   Lala    99       2014-08-03 21:37:17
4   Bash    102      2014-08-04 21:37:17
5   Assem   99       2014-08-05 21:37:17

In this very case, got 3 persons have the same score which are 99. Boo rank would be 3, then Lala, then come to Assem because I want to arrange by submit date.

The query should return one row, which contains (only) the required Rank.

Expected Result should be like below:

Id  Name    Score    SubmitDate              Rank
4   Bash    102      2014-08-04 21:37:17     1
1   Ida     100      2014-08-01 21:37:17     2
2   Boo     99       2014-08-02 21:37:17     3
3   Lala    99       2014-08-03 21:37:17     4
5   Assem   99       2014-08-05 21:37:17     5

Any idea how to accomplish this, I saw a lot solution have the same ranking but I do not want this output. Thanks!

1

Order by Score in descending order, and by SubmitDate in ascending order, then use a variable to give correlative numbers to each row:

mysql> SET @Rank := 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT *, @Rank := @Rank + 1 AS Rank FROM ranking ORDER BY Score DESC, SubmitDate ASC;
+----+-------+-------+---------------------+------+
| Id | Name  | Score | SubmitDate          | Rank |
+----+-------+-------+---------------------+------+
|  4 | Bash  |   102 | 2014-08-04 21:37:17 |    1 |
|  1 | Ida   |   100 | 2014-08-01 21:37:17 |    2 |
|  2 | Boo   |    99 | 2014-08-02 21:37:17 |    3 |
|  3 | Lala  |    99 | 2014-08-03 21:37:17 |    4 |
|  5 | Assem |    99 | 2014-08-05 21:37:17 |    5 |
+----+-------+-------+---------------------+------+
5 rows in set (0.00 sec)
  • if it possible I added where id=5, still the get rank 5? I know @Rank is temporarily variable. any possible to do it? – Tom Kim Aug 5 '14 at 16:25
  • @Tom Kim you could do SELECT * FROM (SELECT *, @Rank := @Rank + 1 AS Rank FROM ranking ORDER BY Score DESC, SubmitDate ASC) rank WHERE Id = 5; but that is "Oracle way of doing things", and not very well optimized in MySQL (it works, but it will be very slow on tall tables). – jynus Aug 5 '14 at 16:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.