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

Sample Data

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');

MySQL Query to Remove Duplicates in Each Sequence

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

How It Works

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.

Resources