1

I'm using this query to fetch a highscore of a certain user.

SELECT score FROM highscores WHERE user_id=?

I would like to know the how-many'th this particular highscore is compared to the other scores. So basically the row number after we DESC ordered the highscores table.

I could of course fetch all rows and look at the array key but I was wondering if there's a more performance friendly method directly via the MySQL query. The highscore table contains around 500k rows, and it seems a bit overkill (and it will take some time) to have to fetch them all first just to get the rank number of the user's score.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
binoculars
  • 2,226
  • 5
  • 33
  • 61

1 Answers1

1

Some time ago I helped a friend with a similar task, and wrote an article, How to get a single player's rank based on the score.

So the idea is to run a count(*) query with a WHERE clause to include all players having a higher score:

SELECT count(*)+1 FROM players WHERE score > ?

or, based on the user id,

SELECT count(*)+1 FROM players WHERE score > (SELECT score FROM players WHERE id = ?)

This solution is much simpler than anything I was able to find on Stack Overflow.

It can be also refined, to take into account other fields, in case two users have the same score, such as time when the score has been updated:

SELECT count(*)+1 FROM players WHERE score > ? OR (score = ? AND score_updated < ?)
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345