lundi 13 avril 2015

SQL leaderboard display

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


Aucun commentaire:

Enregistrer un commentaire