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

Answers


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.

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


Need Your Help

What is the preferred way to access ASP.NET profile in .NET n-tier application?

asp.net .net wcf

I have a WPF application which talks to a WCF service hosted in IIS. I am also using ASP.NET authorization and authentication to access the service methods. There is also a relatively thin web based

Why optimization flag (-O3) doesn't speed up quadruple precision calculations?

c++ compiler-optimization floating-point-precision quadruple-precision

I have a high-precision ODE (ordinary differential equations) solver written on C++. I do all calculations with user-defined type real_type. There is a typedef declaring this type in the header: