Database Reference
In-Depth Information
| 4 | 5 | 00:17:50 | 26 | 01:21:40 | 19.1020 |
+-------+----+----------+---------+----------+--------------+
We can see from this that the runner's average pace increased a little during the second
stage of the race but then decreased thereafter, presumably as a result of fatigue.
15.11. Assigning Ranks
Problem
You want to assign ranks to a set of values.
Solution
Decide on a ranking method, 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 vari‐
ables. 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 |
+-------+
Before you use the ideas presented here, be aware that the queries use
the
unsanctioned
technique of getting and setting the same user-
defined variable within the same statement and therefore might break
some day. An alternative (also illustrated here) is to pull the observa‐
tions into a program that does the ranking calculations.
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:
