Calculate user rank based on two columns

Question

I did wrote a Web Service for a Android game with PHP. The query is used for calculating user's rank is like this:

$rank = mysqli_fetch_row(mysqli_query($db, "SELECT FIND_IN_SET(`score`, (SELECT GROUP_CONCAT(`score` ORDER BY `score` DESC) FROM `scores`)) AS `rank` FROM `scores` WHERE `user_id` =  '{$user_id}'"));

So, what is the problem? Suppose user_a submit his score (for example 1000) and $rank equals to 1, then if user_b submit the same score as user_a, the $rank will be 1 again. I don't want this, in the case of score equality, first user must have the higher rank. So I added a new integer column for submit_date timestamp to scores table and tried to manipulate the query and unfortunately since i'm not professional in SQL, My efforts failed.

Update: I won't store the rank itself in the database. The scores table structure looks like this:

`_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`score` int(11) NOT NULL,
`submit_date` int(11) NOT NULL

The user_id is unique. If a user didn't submitted his score before, a new record created. Else his record will be updated (in case new score is higher than old one).

What should i do?

maybe add ordering by submit date in multiple order with score http://stackoverflow.com/questions/2051162/sql-multiple-column-ordering — pmaniora, Dec 13 '15 at 10:25
@pmaniora Thanks for your comment. I tried this before: GROUP_CONCAT(`score` ORDER BY `score` DESC, `submit_date`), did not worked. — ma3x, Dec 13 '15 at 10:28
GROUP_CONCAT doesn't group by score? then its flatten to 1 maybe try SELECT `score` ? — pmaniora, Dec 13 '15 at 10:45
@pmaniora I tried this: "SELECT FIND_IN_SET(score, (SELECT score ORDER BY score DESC, submit_date FROM scores)) AS rank FROM scores WHERE user_id = '{$user_id}'". Did not worked. — ma3x, Dec 13 '15 at 11:07

score 1 · Answer 1 · edited Dec 13 '15 at 11:13

    If two user with same score and do display old user at top.
    you can add on update date ie when the score is update the current date time will be inserted on that field.
    then when you invoke record from the data base retrieve according to rank and update date
    like: 
    SELECT * FROM test ORDER BY rank DESC, update_date ASC

from this you can get two field can order at a time

Calculate user rank based on two columns

1 Answers1