CODEWITHSUNDEEP
mysqlsqldatabase
Dan
Dan

Reputation: 12096

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

Upvotes: 0

Views: 831

Answers (2)

Arvin Yorro
Arvin Yorro

Reputation: 12107

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

Upvotes: 1

Ravinder Reddy
Ravinder Reddy

Reputation: 23982

You have to use aggregate sum function to get total score by user id.

Try this: 

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

Upvotes: 1

Related Questions