Mysql query : Return a value from a column if the value in another column changes in sequence-CodePudding

I want to return the value of a column whenever the value in adjacent column changes.

---------------------
state      timestamp
FALSE      06:01
FALSE      06:02
TRUE       06:03
TRUE       06:04
FALSE      06:05
------------------

THE QUERY RESULT SHOULD BE

FALSE  06:01
TRUE 06:03
FALSE 06:05

CodePudding user response：

DROP TABLE IF EXISTS T;
CREATE TABLE t
(state varchar(10),     timestamp time);
insert into t values
('FALSE'  ,    '06:01'),
('FALSE'  ,    '06:02'),
('TRUE'   ,    '06:03'),
('TRUE'   ,    '06:04'),
('FALSE'  ,    '06:05');

with cte as
(
select t.* ,lag(state) over (order by timestamp) prevstate
from t
)
select * from cte where state <> prevstate or prevstate is null