Track when a value changed
You have a table that tracks a value and the time when the value was measured ...
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;
(Every week or so, someone on a MySQL forum poses this question, and a forum regular who goes by the name 'laptop alias' posts a solution very like the above.)