Statistical Techniques - MySQL - page 539

mysql> SET @rownum := 0;
mysql> SELECT @rownum := @rownum + 1 AS rank, score
-> FROM t ORDER BY score DESC;
+------+-------+
| rank | score |
+------+-------+
| 1 | 5 |
| 2 | 4 |
| 3 | 4 |
| 4 | 3 |
| 5 | 2 |
| 6 | 2 |
| 7 | 2 |
| 8 | 1 |
+------+-------+
That kind of ranking doesn't take into account the possibility of ties (instances of values
that are the same). A second ranking method does so by advancing the rank only when
values change:
mysql> SET @rank = 0, @prev_val = NULL;
mysql> SELECT @rank := IF(@prev_val=score,@rank,@rank+1) AS rank,
-> @prev_val := score AS score
-> FROM t ORDER BY score DESC;
+------+-------+
| rank | score |
+------+-------+
| 1 | 5 |
| 2 | 4 |
| 2 | 4 |
| 3 | 3 |
| 4 | 2 |
| 4 | 2 |
| 4 | 2 |
| 5 | 1 |
+------+-------+
A third ranking method is something of a combination of the other two methods. It
ranks values by row number, except when ties occur. In that case, the tied values each
get a rank equal to the row number of the first of the values. To implement this method,
keep track of the row number and the previous value, advancing the rank to the current
row number when the value changes:
mysql> SET @rownum = 0, @rank = 0, @prev_val = NULL;
mysql> SELECT @rownum := @rownum + 1 AS row,
-> @rank := IF(@prev_val<>score,@rownum,@rank) AS rank,
-> @prev_val := score AS score
-> FROM t ORDER BY score DESC;
+------+------+-------+
| row | rank | score |
+------+------+-------+
| 1 | 1 | 5 |