How to form an optimal query to get high scores from a data base?

Question

Hi,

I'm using a MySQL database to store scores for my game. A very simplified version of the table would be

(PlayerID - int) (Name - string) (Score - int)

I'd like to form a query that would return me a set of say 10 results where the player of interest is in the middle of the table.

Perhaps an example would make it more clear.

I have just achieved a high score, my name is Steve. When I look at the high score table I'd like to see the 5 scores below me and the 5 scores above me. Obviously if I have the top score I will see the 9 scores below me, and conversely, if I am at the bottom I will see the 9 scores above me. The score table could consist of thousands of scores.

As the database is specifically designed for querying sets of data I'd like to reduce the amount of post processing on the results.

Does anyone have an idea for a query to achieve this?

Thanks Rich

Answer 1

if you have the user id and score (userId and userScore) you can do:

SELECT * FROM Scores 
WHERE PlayerID = userId
OR PlayerID IN (SELECT PlayerID FROM Scores WHERE Score > userScore ORDER BY Score LIMIT 5)
OR PlayerID IN (SELECT PlayerID FROM Scores WHERE Score < userScore ORDER BY Score DESC LIMIT 5)
ORDER BY Score

EDIT: following your comments: First of all, you need to know the rank of the current user. You can know it with such a query as SELECT COUNT(*) FROM Scores WHERE Score > userScore
It gives you a number n
Then you compute two numbers usersUp and usersDown such as usersDown + userUp = 10 and n - userUp>0 and n + usersDown < SELECT COUNT(*) FROM Scores

Then you can use a UNION instead of IN and Subqueries:

SELECT * FROM Scores WHERE PlayerID = userId
UNION
SELECT * FROM Scores WHERE Score > userScore ORDER BY Score LIMIT usersUp
UNION 
(SELECT * FROM Scores WHERE Score < userScore ORDER BY Score LIMIT usersDown)

if you want to stay on the mysql server side you can gather all that in a stored procedure