MySQL - How To Select Rows Depending on Value in Previous Row (Remove Duplicates in Each Sequence)

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

1	2011-12-17 3:17:35	San Mateo
2	2011-12-17 4:23:40	San Mateo
3	2011-12-17 5:40:28	San Bruno
4	2011-12-17 6:30:18	Millbrae
5	2011-12-17 7:33:54	Millbrae
6	2011-12-17 8:40:34	San Bruno
7	2011-12-17 10:20:41	San Mateo

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:

The MySQL query to remove duplicate values in each sequence uses a correlated query to check the city in the previous row.

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.

Using this subquery the get the city in the previous row by id of the previous row.

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.

• MySQL Reference