MySQL Select a user's rank in a dynamically generated leaderboard
I have a table of user predictions which I use to generate a leaderboard of top users and their ranks.
The table (predictions) looks like this:
id | userid | fixtureid | correct 1 | 1 | 1 | 1 2 | 2 | 5 | 0 3 | 2 | 6 | 1 4 | 2 | 7 | 1 5 | 3 | 6 | 0
Currently I use this query to generate the leaderboard and then loop through results:
SELECT userid, (correct * 3) AS score FROM predictions GROUP BY userid ORDER BY score DESC
This would then display a leaderboard similar to this, the rank is calculated in php using $i++; after each row:
User Id | Score | 2 | 6 | #Rank is 1 1 | 3 | #Rank is 2 3 | 0 | #Rank is 3
What I'm trying achieve
I want to be able to find out a user's rank in the leaderboard without building the whole table and just using their userid.
How can I do this bearing in mind that the users could increase to a large number in the future?
Psuedo code of what I'm trying to achieve:
SELECT rank FROM predictions WHERE userid = 3
Which would return:
User Id | Score | 3 | 0 | #Rank is 3
I can't see how you generated the column "Position", but anyways, have you explored the possibility of using a View?
You only need to run this command ONCE:
CREATE VIEW `leaderboard` AS SELECT userId, (correct * 3) AS score FROM predictions GROUP BY userid ORDER BY score DESC
Then you would treat the created view as a normal table in your application.
Here is an example on how you would use it:
SELECT (SELECT COUNT(*) FROM leaderboard WHERE score >= L.score) AS position, L.userid, L.score FROM leaderboard L WHERE L.userid = 3
You have to use aggregate sum function to get total score by user id.
select Rank, userid, score from ( SELECT @rn:=@rn+1 as Rank, userid, sum(correct * 3) AS score FROM predictions , (select @rn:=0) row_nums GROUP BY userid ORDER BY score DESC ) user_ranks where userid=3;
Demo @ SQL Fiddle