Question: There is a table that logs the customer support requests from different cities. How to get the list of cities in the order of requests from them. Note that the same city can be selected multiple times.
For example, the log table content ( marks the rows that need to be selected by the query):
Assume we have the following table definition and data:
CREATE TABLE cities ( id INT AUTO_INCREMENT PRIMARY KEY, req DATETIME, city VARCHAR(80) ); INSERT INTO cities VALUES (NULL, '2011-12-17 3:17:35', 'San Mateo'); INSERT INTO cities VALUES (NULL, '2011-12-17 4:23:40', 'San Mateo'); INSERT INTO cities VALUES (NULL, '2011-12-17 5:40:28', 'San Bruno'); INSERT INTO cities VALUES (NULL, '2011-12-17 6:30:18', 'Millbrae'); INSERT INTO cities VALUES (NULL, '2011-12-17 7:33:54', 'Millbrae'); INSERT INTO cities VALUES (NULL, '2011-12-17 8:40:34', 'San Bruno'); INSERT INTO cities VALUES (NULL, '2011-12-17 10:20:41', 'San Mateo');
The query:
SELECT city FROM cities c WHERE city NOT IN ( SELECT city FROM cities WHERE id = (SELECT MAX(id) FROM cities WHERE id < c.id) ) ORDER BY req;
It returns:
city |
San Mateo |
San Bruno |
Millbrae |
San Bruno |
San Mateo |
The MySQL query to remove duplicate values in each sequence uses a correlated query to check the city in the previous row.
SELECT MAX(id) FROM cities WHERE id < c.id |
This correlated query finds id value of the previous row. It is the largest id among those ids that are less than id of the current row of the correlated query.
SELECT city FROM cities WHERE id = (SELECT MAX(id) … ) |
Using this subquery the get the city in the previous row by id of the previous row.
SELECT city FROM cities c WHERE city NOT IN (…) |
Our main query retrieves rows from the log table and checks that the city in the current row is not equal to the city in the previous row.