score ranking no tie, if tie order by score submit date

Question

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!

Answer 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)