通过SQL查询获得团队排名

Question

I have this SQL query for getting the rank number of a team in my database based on their 'point'.

SELECT id, points, team_name, FIND_IN_SET( points, (
    SELECT GROUP_CONCAT( points
    ORDER BY points DESC )
    FROM teams )
    ) AS rank
    FROM teams
    WHERE id = ?
    LIMIT 1

The problem I'm having is that given the scenario that two teams have the same points e.g. say there is 2 teams and each team has '0' points. The query returns rank '1' for both of the teams.

I want the team with the lower 'id' to have the higher rank between the teams with the same points.

For example say team1 and team2 both have '5' points. I want the team with the lower id to have rank 1.

How can I alter my query to do that?

Thanks

Answer 1

The method you are using has limited applicability in MySQL, because it depends on the length of the intermediate substring in the group_concat(). However, you can modify it for this purpose:

SELECT id, points, team_name,
       FIND_IN_SET(CONCAT(points, ':', id),
                   (SELECT GROUP_CONCAT(points, ':', id ORDER BY points DESC, id )
                    FROM teams
                   )
                  ) AS rank
FROM teams
WHERE id = ?
LIMIT 1;

A better method is:

select id, points, team_name,
       (select count(*)
        from teams t2
        where t2.points > t.points or
              (t2.points = t.points and t2.id <= t.id)
       ) as rank
from teams t
where id = ?;