CODEWITHSUNDEEP
mysqlsql
SteveEdson
SteveEdson

Reputation: 2495

MySQL: How to get leaderboard position, for each event in a series

I have the following data structure (simplified):

Users:
    ID
    Name

Events:
    ID
    Date

Results:
    ID
    User ID (foreign key)
    Event ID (foreign key)
    Points: (int)

I would like to know (ideally the most efficient way):

I can get the leaderboard with:

select users.name, SUM(results.points) as points
from results
inner join users on results.user_id = users.id
group by users.id
order by points DESC

However, I'd like to know a user's position without having to return the entire table if possible.

Edit: I have supplied some sample data here.

Ideal output:

| User ID | Rank  |
| 3       | 1     |
| 1       | 2     |
| 2       | 3     |

and something similar to (not exactly like this, it's flexible, just something that shows the user's rank from each event)

| User ID | After Event | Rank  |
| 1       | 1           | 1     |
| 1       | 2           | 1     |
| 1       | 3           | 2     |
| 2       | 1           | 2     |
| 2       | 2           | 2     |
| 2       | 3           | 1     |
| 3       | 1           | 3     |
| 3       | 2           | 3     |
| 3       | 3           | 3     |

Upvotes: 3

Views: 703

Answers (2)

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17137

MySQL 8.0+ supports window functions so the use of dense_rank() comes in handy.

MySQL under 8.0 solution

Since your version is 5.7 you could imitate this like below:

select 
  t.id,
  CASE WHEN @prevRank = points THEN @currRank
       WHEN @prevRank := points THEN @currRank := @currRank + 1
   END AS rank
from (
  select users.id, SUM(results.points) as points
  from results
  inner join users on results.user_id = users.id
  group by users.id
  order by points DESC
  ) t
  cross join (SELECT @currRank := 0, @prevRank := NULL) r

If you need data for particular user then add a WHERE condition to filter out everyone else in an outer query:

select *
from (
<< above query here >>
) t
where id = ? -- your id here

MySQL 8.0+ solution

rank is a reserved keyword so backticks are required when naming a column. We're using dense_rank window function which will assign ranks based od descending sorting of points acquired:

select id, dense_rank() over (order by points desc) as `rank`
from (
  select users.id, SUM(results.points) as points
  from results
  inner join users on results.user_id = users.id
  group by users.id
  ) t
order by `rank`

Upvotes: 2

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

SET @rowno = 0;
select UserID, max(points), @rowno:=@rowno+1 as rank  from 
(
select users.id as UserID ,users.name as users_name,events.name, SUM(results.points) as points
from results
inner join users on results.user_id = users.id
inner join events on results.event_id= events.id
group by users.id,events.name,users.name
order by points DESC
) as T
group by UserID

order by max(points) desc

Upvotes: 0

Related Questions