MySQL - Return list of top 5 users, while including logged in user
I'm trying to return the users with the top 5 highest scores in a database and assign each user a rank, which I can then display in a high score table. I want the high-score table to contain a maximum of 5 rows, but I also want it to show the rank of the logged-in user amongst the other players. Let's say the logged-in user is 'jennie'.
SELECT * FROM ( SELECT *, @i := @i+1 i, (SELECT COUNT(DISTINCT highscore) FROM users s WHERE s.highscore>=r.highscore) AS rank FROM users r JOIN (SELECT @i:=0) vars ORDER BY highscore DESC ) x WHERE name = 'jennie' OR i <= 5 ORDER BY rank ASC;
(Using MySQL 5.0.96). Here is a fiddle showing off the query. My issue is the query operates differently based on one condition:
1: If jennie is in the top 5 results, 5 rows will be returned, containing jennie amongst the other users.
2: If jennie is not in the top 5 results, 6 rows will be returned, where the last row contains jennie.
How can I use somehow truncate the list of top users in case 2, from 5 users down to 4 users, so when jennie appears on the end she makes up the 5th row? Thanks for your help.
You can achieve your desired result by doing some clever ordering. First we order by whether
rankis <= 4, then by whether the user is
rankis > 5, and then by
rank. This way, if
jennieis outside the top 5, she will show as 5th; if she is inside the top 5, she will show in the appropriate position (because of the final order by
SELECT * FROM (SELECT u1.userid, u1.name, COUNT(u2.userid) + 1 AS rank FROM users u1 LEFT JOIN users u2 ON u2.highscore > u1.highscore GROUP BY u1.userid) r ORDER BY rank <= 4 DESC, name = 'jennie' AND rank > 5 DESC, rank LIMIT 5;
Output (for your sample data)
userid name rank 4 harry 1 7 phil 2 2 james 3 1 sam 4 6 jennie 7
Output (for when jennie's score is 40)
userid name rank 4 harry 1 7 phil 2 6 jennie 3 2 james 4 1 sam 5
Note I prefer to do ranking via
LEFT JOINqueries, as variables can be problematic...