MariaDB: SQL to remove duplicates between different timestamps-CodePudding

I have the following table and want to remove duplicates between the oldest and newest timestamp.

timestamp	type	state
2019-10-01 04:51:42	mod	off
2019-10-01 04:54:42	mod	off
2019-10-01 04:56:42	mod	off
2019-10-01 05:01:42	mod	on
2019-10-01 05:21:42	mod	on
2019-10-01 05:30:42	mod	on
2019-10-01 05:43:42	mod	off
2019-10-01 05:51:42	mod	off
2019-10-01 06:02:42	mod	off
2019-10-01 06:05:42	mod	off
2019-10-01 06:10:42	mod	on
2019-10-01 06:15:42	mod	on
2019-10-01 06:25:42	mod	on

This is the result what I want with a generic SQL statement. I need the first timestamp if status is changed.

timestamp	type	state
2019-10-01 04:51:42	mod	off
2019-10-01 05:01:42	mod	on
2019-10-01 05:43:42	mod	off
2019-10-01 06:10:42	mod	on

Thanks for help :-)

CodePudding user response：

See this DBFIDDLE

SELECT `timestamp`,type,state
FROM (
  SELECT 
    `timestamp`,
    type,
    state,
    LEAD(state) OVER (ORDER BY `timestamp`) as Next,
    LAG(state) OVER (ORDER BY `timestamp`) as Previous
  FROM t1
  ) x
WHERE state=Next AND (state<>Previous OR Previous is null);