Fetch distinct / different from previos row Data from MySQL Table

Question

I have a simple Table. It contains an ID, a Temperature and a Date. Every 10 Seconds a Sensor writes a Row with the measured Temperature-Value.

What I need is the Data, where the temperature changed. For Example:

ID, Temp, Date
1, 10, 2023年03月01日_13_00_00
2, 10, 2023年03月01日_13_00_10 <= Same as previos, should be filtered out
3, 11, 2023年03月01日_13_00_20
4, 10, 2023年03月01日_13_00_30
5, 10, 2023年03月01日_13_00_40 <= Same as previos, should be filtered out
6, 10, 2023年03月01日_13_00_50 <= Same as previos, should be filtered out
7, 9, 2023年03月01日_13_01_00

Is it possible to do it in MySQL right away?

• mustaccio: What version of MySQL? Please consider following these suggestions.
• Rick James: If you are not yet using 8.0, a "self-join".

Answer 1

You can use LAG function, but it requires MySQL 8+.

Consider the following data.

CREATE TABLE my_table (
 id int,
 temp int,
 dt timestamp ) ;
insert into my_table values 
(1,10,'2023-03-01 13:00:00'),
(2,10,'2023-03-01 13:00:10'),
(3,11,'2023-03-01 13:00:20'),
(4,10,'2023-03-01 13:00:30'),
(5,10,'2023-03-01 13:00:40'),
(6,10,'2023-03-01 13:00:50'),
(7,9,'2023-03-01 13:01:00');

With LAG we can find the difference between current and next row.

SELECT id,
 temp,
 dt,
 LAG(temp, 1, 0) OVER (ORDER BY id asc) - temp as diff
FROM my_table

Result

id temp dt diff
1 10 2023年03月01日 13:00:00 -10
2 10 2023年03月01日 13:00:10 0
3 11 2023年03月01日 13:00:20 -1
4 10 2023年03月01日 13:00:30 1
5 10 2023年03月01日 13:00:40 0
6 10 2023年03月01日 13:00:50 0
7 9 2023年03月01日 13:01:00 1

Final query. Get only the rows where diff <> 0

SELECT id,
 temp,
 dt
FROM ( SELECT id,
 temp,
 dt,
 LAG(temp, 1, 0) OVER (ORDER BY id asc) - temp as diff
 FROM my_table
 ) x 
WHERE diff <> 0;

Result

id temp dt
1 10 2023年03月01日 13:00:00
3 11 2023年03月01日 13:00:20
4 10 2023年03月01日 13:00:30
7 9 2023年03月01日 13:01:00

Check this working example

Reference