使用MySQL从有序列表中返回行号

Question

I'm working on implementing a "High Scores" section in my game. For the high scores, I'm only displaying the top ten. That is no problem. The problem is that I want to also show the user what his rank is. Say the user is ranked 300, is there a way for MySQL to order the list, find the username, and somehow return how far down the list he is or will I just have to copy the entire database into an array or something and count the rows until I hit the user?

Answer 1

Well, if you are only displaying the top 10, I think the simplest and quickest way is to assign the rank in PHP. So, you fetch the list of top 10 scorers in desc order of scores, read the list into a PHP array and the rank will be the (array index + 1).

However, if you wish to assign ranks to all the users there is a way of doing this; I used this in one of my implementations. I'm not sure though if there is as well a direct way of doing so. Here:

1. create a temporary table, say user_rank with the following columns:
   • id PK AUTO_INCREMENT
   • user_id
   • rank SMALLINT UNSIGNED
2. retrieve a list of all your users in desc order of their scores, so the top scorers are on the top
3. store the list from step2 in the temp table user_rank
4. user_rank.id gives you the rank for every user

You may do steps2-3 in the same query, like:

INSERT INTO `user_rank` (`user_id`, `rank`)
SELECT `id`, `score`
FROM `high_scores`
ORDER BY `score` DESC;

Hope this helps.