MySQL Get Rank from Leaderboards

Question

I am implementing leaderboards in a game based on very good tutorial.

http://gamedevelopment.tutsplus.com/tutorials/how-to-code-a-self-hosted-phpsql-leaderboard-for-your-game--gamedev-11627

I am quite new to MySQL, but I got some basics. But there is a part of the code which I am totally clueless how actually works and because I don't want to implement something which doesn't make any sense to me I wanted to ask if someone could please help me understand this. It handles returning player's rank in the leaderboards:

SELECT  uo.*,
    (
    SELECT  COUNT(*)
    FROM    Scores ui
    WHERE   (ui.score, -ui.ts) >= (uo.score, -uo.ts)
    ) AS rank
FROM    Scores uo
WHERE   name = '$name';

My understanding is that first everything in the Scores table gets selected, then amount of rows gets selected, I don't understand how that selection works tho, how is the WHERE working is totaly beyond me with the timestamp. And then I am not sure how it works all together.

Roz

Answer 1

When the database executes this query, first it selects from Scores, filtering by name = '$name'.

Then, for every row, it executes the subquery:

(
 SELECT  COUNT(*)
   FROM    Scores ui
  WHERE   (ui.score, -ui.ts) >= (uo.score, -uo.ts)
) AS rank

It means that, for every row of Scoreswith the searched name, it searches how many records are in Scores where (ui.score, -ui.ts) is greater or equals to the current row's values.

I hope to have helped you...

Josefajosefina

Thanks, that helps a bit. So basicly just to repeat what you said :) It first finds the user, gets his data ( score and timeStamp ), then it compares this data to all the other data in the Score table and counts how many of them is bigger or equal to the user's score/timestamp and that gives back the rank ? That seems to make a sense. Just if could please shed a bit more light for me on the (ui.score, -ui.ts) >= (uo.score, -uo.ts). It would make perfect sense with the score but that negative TimeStamp is confusing me there a bit. Btw does both of those conditions need to be met ? – Roz

Yes, both conditions need to be met. It is counting all higher scores at the time when this row was inserted, so future scores don't alter this one's rank. It has a - just to invert the condition. – Lignite

Oh, so basicly new scores added from new players dont affect players rank because they are newer? Hmm, that doesnt make much sense to me why would i want that. Is there any way how to make it so that the rank is always updated properly as of its actual latest position in the leaderboards? I.e. it seems to me in this case if there is 10 new players with higher score i am still getting higher rank than them because my score was posted earlier ? Would i just remove the Timestamp check to make sure my rank is correct all no matter when the score was posted ? – Roz

Well, simply change the condition for: (ui.score >= uo.score) – Lignite

great, thanks a lot for your help. You saved me from scratching my head for next few hours :) – Roz

Answer 2

A fact from algebra is that -A >= -B if-and-only-if A <= B.

So the expression (ui.score, -ui.ts) >= (uo.score, -uo.ts)

is just a fancy way of checking that ui.score >= ui.score AND ui.ts <= uo.ts.

The reason for including the timestamp is so that there's a "tie breaker" to uniquely define a rank.

I'm not sure if using the (A, -B) >= (A2, -B2) provides any performance advantage over just writing (A >= A2) AND (B <= B2).

Katleen