Reputation: 299
So I am querying a mysql database for a leaderboard of results shown below:
SELECT ur.user_id, ur.race_id, u.name, ur.race_points, ur.points_total,
FIND_IN_SET(points_total, (
SELECT GROUP_CONCAT( points_total
ORDER BY points_total DESC )
FROM user_results)) as Rank
FROM user_results as ur
LEFT JOIN users as u
ON u.id = ur.user_id
LEFT JOIN user_leagues as ul
ON ur.user_id = ul.user_id
JOIN leagues as l
ON ul.league_id = l.id
WHERE l.id = 1
GROUP BY ur.race_id
ORDER BY ur.race_id ASC
Result set looks like this
user_id, race_id, name, race_points, points_total, Rank
2 1 Jess 350 789 2
1 1 Ali 350 667 4
2 2 Jess 666 766 3
1 2 Ali 456 999 1
The problem I am having is I need the RANK column created by the FIND IN SET function to be applied along with the GROUP BY's. Its ranking the whole result set when i need it to rank with the GROUP BY race_id applied as well. So for each race_id the RANK column should be either 1 or 2 as there is only two users.
Any help would be great thanks.
Upvotes: 0
Views: 50
Reputation: 1271241
You want a correlated subquery:
FIND_IN_SET(points_total, (
SELECT GROUP_CONCAT( points_total ORDER BY points_total DESC )
FROM user_results ur2
WHERE ur2.race_id = ur.race_id)) as Rank
There are other ways to accomplish this without string manipulation, but this should fix your particular problem.
Upvotes: 1