I've got a database with 120k players. Each entry contains id, score (and more). The goal is to get a highscore-list of not the top players, but instead of the N players above and and below a player, given his ID.
I currently try to solve this using two queries.
Query 1:
SELECT (
SELECT COUNT(*)
FROM players p2
WHERE p2.score > p1.score
) AS rank
FROM players p1
WHERE id = ID
returns the rank RANK of the player with an offset of -1. (for the best player it'll return 0)
Query 2:
SELECT id, score
FROM players
ORDER BY score DESC
LIMIT X OFFSET RANK;
returns a list with X=2*N+1 entries. I shift the $rank by -n to have the player that is doing the request in the middle (n players higher, current player, n players below).
So far, so good.
The actual issue now is, that for some scores there are more players with this score than X is big, which sometimes results in the player that should be in the middle of the list not even being contained in the X entries, but in some entries above or below.
To me it seems like a consistency problem, that query 1 returns a rank Y for player Z, but query 2 doesn't have player Z at it's Y'th position.
Can these queries be merged, or is there any other nice solution to this?
If the above stated is not clear, here's a minimalistic example:
n=1, requesting player called: C
database: A:123, B:123, C:123, D:123
Query 1 returns rank 3 for player C
Query 2 returns A:123, B:123, D:123 (being ranks 2-4)
C:123 should be in the middle, but the sorting of query 2 had C as rank 1.
The order of the elements with the same score in query 2 seems randomly
