[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)