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!