MySQL – Ranking Columns


[adsense id=”0514458240″ width=”468″ height=”60″]

MySQL unfortunately does not provide any RANK() function yet! However, using user session variables it is possible to get a ranking.

Let us imagine we want to get a rank based on the age of a person:


SELECT    first_name,
          age
          @Rank := @Rank + 1 AS rank
FROM      person p, (SELECT @Rank := 0) r
ORDER BY  age;

The (SELECT @Rank := 0) initialises the variable without requiring a separate SET command. This could also have been done as follows:

SET @Rank := 0;
SELECT    first_name,
          age,
          @Rank := @Rank + 1 AS rank
FROM      person p
ORDER BY  age;

Now let’s look at this in action.


CREATE TABLE person (id int, first_name varchar(20), age int);

INSERT INTO person VALUES (1, 'Tom', 25);
INSERT INTO person VALUES (2, 'Tim', 20);
INSERT INTO person VALUES (3, 'Jack', 30);
INSERT INTO person VALUES (4, 'John', 32);
INSERT INTO person VALUES (5, 'Brian', 22);
INSERT INTO person VALUES (6, 'Jane', 18);
INSERT INTO person VALUES (7, 'Bob', 36);
INSERT INTO person VALUES (8, 'Sally', 26);

mysql> SELECT    first_name,
    ->           age,
    ->           @Rank := @Rank + 1 AS rank
    -> FROM      person p
    -> ORDER BY  age;
+------------+------+------+
| first_name | age  | rank |
+------------+------+------+
| Jane       |   18 |    1 |
| Tim        |   20 |    2 |
| Brian      |   22 |    3 |
| Tom        |   25 |    4 |
| Sally      |   26 |    5 |
| Jack       |   30 |    6 |
| John       |   32 |    7 |
| Bob        |   36 |    8 |
+------------+------+------+
8 rows in set (0.00 sec)


Now this works well but what if we have two entries with the same rank. For example let’s imagine in the example above we add Billy with an age of 25 same as Tom. In this case we want both Tom and Billy to have a rank of 4 and Sally with a rank of 6 (skipping the rank 5).

In this case we will need to keep track of the previous rank and see

INSERT INTO person VALUES (9, 'Billy', 25);

SELECT    first_name,
          age,
          CASE
          WHEN @prev_Age = age THEN @Counter
          WHEN @prev_Age <> age THEN @Counter := @Counter + 1
          END AS rank,
          @prev_Age := age
FROM      person p, (SELECT @Counter := 0) c, (SELECT @prev_Rank := NULL) r, (SELECT @prev_Age := 0) a
ORDER BY  age;

+------------+------+------+------------------+
| first_name | age  | rank | @prev_Age := age |
+------------+------+------+------------------+
| Jane       |   18 | 1    |               18 |
| Tim        |   20 | 2    |               20 |
| Brian      |   22 | 3    |               22 |
| Tom        |   25 | 4    |               25 |
| Billy      |   25 | 4    |               25 |
| Sally      |   26 | 5    |               26 |
| Jack       |   30 | 6    |               30 |
| John       |   32 | 7    |               32 |
| Bob        |   36 | 8    |               36 |
+------------+------+------+------------------+
9 rows in set (0.01 sec)

In this case we have defined an order with both Tom and Sally with the same age having the same rank. However, we need to modify this to display actual rank so Jack should show rank 6 and not 5 as is the case here.


SELECT    first_name,
          age,
          CASE
          WHEN @prev_Age = age THEN @prev_Rank
          WHEN @prev_Age <> age THEN @prev_Rank := @Counter
          END AS rank,
          @prev_Age := age as age,
          @Counter := @Counter + 1 as counter
FROM      person p, (SELECT @Counter := 1) c, (SELECT @prev_Rank := 1) r, (SELECT @prev_Age := 0) a
ORDER BY  age;

+------------+------+------+------+---------+
| first_name | age  | rank | age  | counter |
+------------+------+------+------+---------+
| Jane       |   18 | 1    |   18 |       2 |
| Tim        |   20 | 2    |   20 |       3 |
| Brian      |   22 | 3    |   22 |       4 |
| Tom        |   25 | 4    |   25 |       5 |
| Billy      |   25 | 4    |   25 |       6 |
| Sally      |   26 | 6    |   26 |       7 |
| Jack       |   30 | 7    |   30 |       8 |
| John       |   32 | 8    |   32 |       9 |
| Bob        |   36 | 9    |   36 |      10 |
+------------+------+------+------+---------+
9 rows in set (0.00 sec)