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?

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