MySQL Rank in the Case of Ties

Question

I need some help dealing with ties when ranking in MySQL. For example:

PLAYER | POINTS

• Mary: 90
• Bob: 90
• Jim: 65
• Kevin: 12

Bob and Mary should both be ranked #1. Jim should be #3. Kevin should be #4.

MySQL:

SET @rank=0;
SELECT @rank:=@rank +1 as rank, player, points FROM my_table

How can I change the SELECT statement so that the ranking is correct in the case of ties?

My real life problem is more complicated, but if I understand how to solve the above, then I should be set.

Answer 1

SELECT players.*, COUNT(higher_ranking.id) + 1 AS rank
    FROM players
    LEFT JOIN players AS higher_ranking
        ON higher_ranking.points > players.points
    GROUP BY players.id

On Postgres, you could use window functions RANK() to achieve this, which is much nicer. I don't know of anything like that for MySQL.

Answer 2

Assuming name is unique

SELECT t1.name, (SELECT COUNT(*) FROM table_1 t2 WHERE t2.score > t1.score) +1
AS rnk
FROM table_1 t1