[adsense id=”0514458240″ width=”468″ height=”60″]
Let’s imagine that we have a table with the following entries and we would like to add a ranking based on the country_id and the id. However, for each new country_id we need to reset the rnk to 1 for the first entry. This is an update that requires to be executed once the table has been populated or modified and does not need to be automated through a trigger.
CREATE TABLE tbCities (
id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
country_id INTEGER UNSIGNED,
title VARCHAR(100),
rnk INTEGER
) ENGINE = InnoDB;
INSERT INTO `tbCities` (`title`, `country_id`) VALUES
('New York', '1'),('Chicago', '1'),('Los Angeles', '1'),('Miami', '1'),
('London', '2'),('Liverpol', '2'),('Manchester', '2');
In order to solve this we need to use 2 user session variables. @rank and @ctryid. We need to initialize these:
mysql> SET @rank = 0; Query OK, 0 rows affected (0.00 sec) mysql> SET @ctryid = 0; Query OK, 0 rows affected (0.00 sec)
We need both of these as we need to keep track of the rank without each country and we also need to check that if a country_id changes that we reset the rank to 1.
If we look at a SELECT statement that returns the rank we have:
mysql> SELECT id, IF(country_id <> @ctryid, @rank := 1, @rank := @rank + 1) as rank, @ctryid := country_id FROM tbCities ORDER BY id; +----+------+-----------------------+ | id | rank | @ctryid := country_id | +----+------+-----------------------+ | 1 | 1 | 1 | | 2 | 2 | 1 | | 3 | 3 | 1 | | 4 | 4 | 1 | | 5 | 1 | 2 | | 6 | 2 | 2 | | 7 | 3 | 2 | +----+------+-----------------------+ 7 rows in set (0.00 sec)
The IF statement in the SELECT statement checks to see if the country_id is different from the user session variable @ctryid. If it is then we reset the @rank to 1. If it is the same then we increment the @rank by 1.
The order in which the user session variables are presented in the SELECT statement is the order in which these will be modified. Hence, adding the @ctryid assignment as the last field in the SELECT statement implies that this is assigned after the IF statement has processed. This has been done so that the next row the IF statement will use the previous rows country_id to compare with.
The ORDER BY clause is important to make sure that the rank is calculated correctly.
Now we need to perform an UPDATE on the table using this query:
mysql> SET @rank = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> SET @ctryid = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE tbCities
-> INNER JOIN (SELECT id, IF(country_id <> @ctryid, @rank := 1, @rank := @rank + 1) as rank, @ctryid := country_id FROM tbCities ORDER BY id) AS ranks ON (ranks.id = tbCities.id)
-> SET tbCities.rnk = ranks.rank;
Query OK, 7 rows affected (0.03 sec)
Rows matched: 7 Changed: 7 Warnings: 0
mysql> SELECT * FROM tbCities;
+----+-------------+------------+------+
| id | title | country_id | rnk |
+----+-------------+------------+------+
| 1 | New York | 1 | 1 |
| 2 | Chicago | 1 | 2 |
| 3 | Los Angeles | 1 | 3 |
| 4 | Miami | 1 | 4 |
| 5 | London | 2 | 1 |
| 6 | Liverpol | 2 | 2 |
| 7 | Manchester | 2 | 3 |
+----+-------------+------------+------+
7 rows in set (0.00 sec)