0

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.

user229044
  • 232,980
  • 40
  • 330
  • 338
Laxmidi
  • 2,650
  • 12
  • 49
  • 81

2 Answers2

2
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.

shesek
  • 4,584
  • 1
  • 28
  • 27
  • Are you sure about your query? It returns me "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*)" – Nicola Cossu Aug 17 '11 at 22:21
  • I benchmarked both queries, a1ex07's query is faster. I did expect a JOIN to work better here... I'm not sure why its faster with a subquery. – shesek Aug 17 '11 at 22:31
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
a1ex07
  • 36,826
  • 12
  • 90
  • 103
  • Thanks so much for the message. As I understand it, the t2 is an alias for table_1? Did I understand it correctly? – Laxmidi Aug 17 '11 at 22:31