Track when a value changed

from the Artful Common Queries page


You have a table that tracks a value and the time when the value was measured. You don't want an audit log, just a change log for this table ...
drop table if exists changes;
create table changes(time time,value int);
insert into changes values
('00:00', 0 ),
('01:05', 1 ),
('01:09', 1 ),
('01:45', 1 ),
('02:24', 0 ),
('12:20', 1 ),
('12:40', 0 ),
('14:32', 0 ),
('18:20', 1 ),
('18:21', 1 ),
('20:40', 0 );
and you need a query to show the rows where the value changes. For how to retrieve a rank ordering we can subsequently use, see Rank order. Here we need rank ordering on time. We get it by joining each row R to rows with equal or earlier times and counting those rows:
DROP TABLE IF EXISTS tmp;
CREATE TABLE tmp
  SELECT a.*, COUNT(a.value) AS rank
  FROM changes a
  JOIN changes b ON a.time >= b.time
  GROUP BY a.time;
SELECT * FROM tmp;
+----------+-------+------+
| time     | value | rank |
+----------+-------+------+
| 00:00:00 |     0 |    1 |
| 01:05:00 |     1 |    2 |
| 01:09:00 |     1 |    3 |
| 01:45:00 |     1 |    4 |
| 02:24:00 |     0 |    5 |
| 12:20:00 |     1 |    6 |
| 12:40:00 |     0 |    7 |
| 14:32:00 |     0 |    8 |
| 18:20:00 |     1 |    9 |
| 18:21:00 |     1 |   10 |
| 20:40:00 |     0 |   11 |
+----------+-------+------+
Now if we left join this result to itself on matching values and the left rank >= the right rank, and if we exclude rows with NULLs on the right side, we are left with just the rows for which the next value is different:
SELECT a.time, a.value
FROM tmp a
LEFT JOIN tmp b ON a.value=b.value AND a.rank=b.rank+1
WHERE b.rank IS NULL;
+----------+-------+
| time     | value |
+----------+-------+
| 00:00:00 |     0 |
| 01:05:00 |     1 |
| 02:24:00 |     0 |
| 12:20:00 |     1 |
| 12:40:00 |     0 |
| 18:20:00 |     1 |
| 20:40:00 |     0 |
+----------+-------+
On MySQL 4.1 or later, this can all be done in one step:
SELECT a.time, a.value
FROM (
  SELECT t1.*, COUNT(*) AS rank
  FROM changes t1
  LEFT JOIN changes t2 ON t1.time >= t2.time
  GROUP BY t1.time
) AS a
LEFT JOIN (
  SELECT t1.*, COUNT(*) AS rank
  FROM changes t1
  LEFT JOIN changes t2 ON t1.time >= t2.time
  GROUP BY t1.time
) AS b ON a.rank = b.rank+1 AND a.value = b.value
WHERE b.time IS NULL
ORDER BY a.time;

Return to the Artful Common Queries page