Let's say I have a simple database like:
CREATE TABLE test (
id INT,
time datetime,
status integer
);
and some data:
INSERT INTO test (id, time, status) VALUES (1, '2020-11-09 10:00', 256);
INSERT INTO test (id, time, status) VALUES (2, '2020-11-09 11:00', 256);
INSERT INTO test (id, time, status) VALUES (3, '2020-11-09 11:20', 512);
INSERT INTO test (id, time, status) VALUES (4, '2020-11-09 11:35', 512);
INSERT INTO test (id, time, status) VALUES (5, '2020-11-09 11:40', 1024);
INSERT INTO test (id, time, status) VALUES (6, '2020-11-09 11:45', 1024);
INSERT INTO test (id, time, status) VALUES (7, '2020-11-09 11:48', 1024);
INSERT INTO test (id, time, status) VALUES (8, '2020-11-09 12:00', 0);
INSERT INTO test (id, time, status) VALUES (9, '2020-11-09 12:01', 0);
INSERT INTO test (id, time, status) VALUES (10, '2020-11-09 12:05', 0);
INSERT INTO test (id, time, status) VALUES (11, '2020-11-09 12:07', 0);
INSERT INTO test (id, time, status) VALUES (12, '2020-11-09 12:09', 512);
What I want to do, is to have only the rows when status have changed. Simple DISTINCT gives me only 1 value so is not that easy:
SELECT DISTINCT(status), time FROM test;
So my expected result should have id: 1, 3, 5, 8 and 12
Do I need grouping? Will appreciate the help.
Here is SQL Fiddle: https://www.db-fiddle.com/f/nixj1LCKLJCfeXk9q7233P/0
You can use lag():
select *
from (
select t.*, lag(status) over(order by time) lag_status
from test t
) t
where not status <=> lag_status
This requires MySQL 8.0. In earlier versions, one approach uses a correlated subquery:
select t.*
from test t
where not status <=> (
select t1.status
from test t1
where t1.time < t.time
order by t1.time desc
limit 1
)