Mysql – rolling-up consecutive duplicates

MySQLmysql-5.6

Is there a way to "roll up" duplicate records such as time-series data in MySQL? I'm currently doing this in the application code after retrieving full time-series but the transmission of mostly redundant rows seems a nuisance. Here is an example:

Original data:

 date | state
..30  | A
..29  | A
..28  | A
..27  | D
..26  | D
..25  | A

Resultset with "rolled up" consecutive duplicate records:

 date | state
..28  | A
..26  | D
..25  | A

Thanks!

Best Answer

This code assumes that there will only be one record for any given date. It also does not check to make sure that dates are consecutive, if that's important.

SELECT the_date, my_state as the_state
  FROM (SELECT the_date
              ,if(@laststate = the_state, '', the_state) as my_state
              ,@laststate := the_state
          FROM myTest
         ORDER BY the_date
       ) sq
 WHERE my_state <> ''
 ORDER BY the_date DESC
;

First, in the subquery, it sets the state to an empty string if it's the same as the state in the previous row. Then, we select only those records form the subquery where the state is not an empty string.

See in db-fiddle.com.

Related Question