toby's Journal: Uses for SELF JOIN: 3) rankings

From Xgc on #mysql comes this neat way to rank using a self join.

mysql> select * from player;
+----+-------+
| id | score |
+----+-------+
| 1 | 10 |
| 2 | 20 |
| 3 | 6 |
| 4 | 60 |
| 5 | 16 |
+----+-------+
5 rows in set (0.00 sec)

mysql> select L.id,L.score,count(R.id)+1 as rank
from player L left join player R on R.score > L.score
group by L.id order by rank;
+----+-------+------+
| id | score | rank |
+----+-------+------+
| 4 | 60 | 1 |
| 2 | 20 | 2 |
| 5 | 16 | 3 |
| 1 | 10 | 4 |
| 3 | 6 | 5 |
+----+-------+------+
5 rows in set (0.00 sec)

By comparing two columns at once, we can define how to rank equal scores (break ties):

mysql> select L.id,L.score,count(R.id)+1 as rank from player L left join player R on (R.score,L.id)>(L.score,R.id) group by L.id order by rank;
+----+-------+------+
| id | score | rank |
+----+-------+------+
| 4 | 60 | 1 |
| 2 | 20 | 2 |
| 5 | 16 | 3 |
| 7 | 16 | 4 |
| 1 | 10 | 5 |
| 3 | 6 | 6 |
+----+-------+------+
6 rows in set (0.01 sec)

In this case, a lower 'id' wins the tie (just like on /.).

Using this trick to physically 're-number' a column according to the order of another column:

mysql> select * from u;
+------+------+------+
| d | id | s |
+------+------+------+
| NULL | 1 | NULL |
| NULL | 2 | NULL |
| NULL | 3 | NULL |
| NULL | 4 | NULL |
| NULL | 21 | NULL |
| NULL | 10 | NULL |
| NULL | 11 | NULL |
| NULL | 13 | NULL |
| NULL | 19 | NULL |
| NULL | 22 | NULL |
| NULL | 23 | NULL |
+------+------+------+
11 rows in set (0.00 sec)

mysql> update u join ( select L.id,count(*) as rank from u L join u R on R.id<=L.id group by L.id ) d on u.id=d.id set s = d.rank;
Query OK, 11 rows affected (0.00 sec)
Rows matched: 11 Changed: 11 Warnings: 0

mysql> select * from u;
+------+------+------+
| d | id | s |
+------+------+------+
| NULL | 1 | 1 |
| NULL | 2 | 2 |
| NULL | 3 | 3 |
| NULL | 4 | 4 |
| NULL | 21 | 9 |
| NULL | 10 | 5 |
| NULL | 11 | 6 |
| NULL | 13 | 7 |
| NULL | 19 | 8 |
| NULL | 22 | 10 |
| NULL | 23 | 11 |
+------+------+------+
11 rows in set (0.00 sec)