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

Question

I use a MySQL database to store scores for my game. A very simplified version of the table will be

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

I would like to form a query that will return me a set of 10 results, in which the player of interest is in the middle of the table.

Perhaps an example will make it more understandable.

I just got a high score, my name is Steve. When I look at the table of high scores, I would like to see 5 points below me and 5 points above me. Obviously, if I have a top score, I will see 9 points below me, and vice versa, if I am below, I will see 9 points above me. The scorecard may consist of thousands of points.

Since the database is specifically designed for querying datasets, I would like to reduce the amount of post-processing of the results.

Does anyone have any ideas for a request?

Thanks rich

Answer 1

if you have a user id and rating (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 find out with a query such as SELECT COUNT(*) FROM Scores WHERE Score > userScore
This gives you the number n. Then you calculate 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 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)

mysql,