MySQL Score Ranking – No Tie, Order by Score Submit Date

MySQL

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!

Best Answer

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)

Related Solutions

MySQL – How to Get the Rank of a User in a Score Table

SELECT id, name, score, FIND_IN_SET( score, (
SELECT GROUP_CONCAT( score
ORDER BY score DESC ) 
FROM scores )
) AS rank
FROM scores

gives this list:

id name  score rank
1  Ida   100   2
2  Boo    58   5
3  Lala   88   4
4  Bash  102   1
5  Assem  99   3

Getting a single person score:

SELECT id, name, score, FIND_IN_SET( score, (    
SELECT GROUP_CONCAT( score
ORDER BY score DESC ) 
FROM scores )
) AS rank
FROM scores
WHERE name =  'Assem'

Gives this result:

id name score rank
5 Assem 99 3

You'll have one scan to get the score list, and another scan or seek to do something useful with it. An index on the score column would help performance on large tables.

Mysql – Save dataset within another table best practice

If I understand well, you need the bill to still have the person's data when it was created. And if the person changed it's data, like address, you need the bill to keep having the original address where that person was billed.

In other words, you will have different 'versions' of the person. What I would is the following:

Divide person table into two tables with master-details (or one to many) relationship. In the master table, have the fields that do not change, like ID, Name. In the details table, have a primary key field and the ID from master and other fields that may change, like address.

Person(ID primary key, name , ...)
Person_det(ID PK, Person_id referencing person.id, address, ...)

'Bill' table now would have "person_det.id" as a foreign key, instead of person.id.

Best Answer

Related Solutions

MySQL – How to Get the Rank of a User in a Score Table

Mysql – Save dataset within another table best practice

Related Question