CODEWITHSUNDEEP
mysqlsql
user969729
user969729

Reputation: 299

MYSQL applying a group by to a rank column

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

Answers (1)

Gordon Linoff
Gordon Linoff

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

Related Questions