Assigning Ranks
Problem
You want to assign ranks to a set of values.
Solution
Decide on a ranking method, and then put the values in the desired order and apply the method to them.
Discussion
Some kinds of statistical tests require assignment of ranks.
This section describes three ranking methods and shows how each can be
implemented by using user-defined variables. The examples assume that
a table t
contains the following
scores, which are to be ranked with the values in descending
order:
mysql>SELECT score FROM t ORDER BY score DESC;
+-------+
| score |
+-------+
| 5 |
| 4 |
| 4 |
| 3 |
| 2 |
| 2 |
| 2 |
| 1 |
+-------+
One type of ranking simply assigns each value its row number within the ordered set of values. To produce such rankings, keep track of the row number and use it for the current rank:
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 | ...
Get MySQL Cookbook, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.