I'm trying to display the first 15 players of my leaderboard but I also want to ensure that the player who requested the leaderboard is shown at it, with only one SQL request
Lets reduce the board to 4 players only.
Imagine that I'm (who requested to view the board) ranked on the 2nd place, the board should display the following
pos,name
1,bob
2,ME
3,tony
4,andy
But lets imagine that I'm ranked at 150th, then the board should come as
pos,name
1,bob
2,tony
3,andy
150,ME
I'm using the following request:
SELECT * FROM
(
SELECT name,rating,
(SELECT COUNT(*)
FROM rank AS t2
WHERE t2.rating > t1.rating)
AS position FROM rank AS t1
LIMIT 15
)
UNION
SELECT name,rating,(SELECT COUNT(*)
FROM rank AS t2
WHERE t2.rating > t1.rating)
AS position FROM rank AS t1
WHERE t1.name = 'ben'
ORDER BY rating DESC
If I'm ranked between the first 15th the board show correctly. But If I'm past the first 15th the query return 16 rows with me being the last
In short, I want to display 15 players at the leaderboard but one of them MUST be me
edit: Note that two players can be at same rank position
SELECT * FROM
(
SELECT name,rating,
(SELECT COUNT(*)
FROM rank AS t2
WHERE t2.rating > t1.rating
AND t1.name <> 'ben')
AS position FROM rank AS t1
LIMIT 14
)
UNION
SELECT name,rating,(SELECT COUNT(*)
FROM rank AS t2
WHERE t2.rating > t1.rating)
AS position FROM rank AS t1
WHERE t1.name = 'ben'
ORDER BY rating DESC
Here, instead don't return your own user in the first query and limit it to 14. Then in the second query only return your own user.