Search code examples
sqlitedistinct

DIstinct Serial Number with value updates

Hello thanks for accepting my question, i have below data collected from sensors and i need to get only latest changes from all data, i am using sqlite with sqliteStuidio, please help me with the query that i can use to get Expected data. thanks in advance. Please see the image as table is not properly formatted.
data image

Input data :

Serial Key Time_Stamp value
0 12 20230531010945 1
0 11 20230531010912 0
0 10 20230531010657 1
0 9 20230531010648 0
1 8 20230531010551 1
1 7 20230531010539 0
1 6 20230531010521 0
0 5 20230531010346 0
0 4 20230531000935 1
0 3 20230531000928 0
1 2 20230531000724 1
0 1 20230531000652 1

Expected Data :

Serial Key Time_Stamp value
0 12 20230531010945 1
1 8 20230531010551 1

So far i have tried some combinations but failed...

SELECT sensor_data.[sensor_serial], sensor_data.[key],sensor_data.[time], sensor_data.[value]
FROM sensor_data
WHERE sensor_serial = (SELECT  sensor_serial FROM sensor_data)
ORDER BY time DESC

its output is :

0   12  20230531010945  1
0   11  20230531010912  0
0   10  20230531010657  1
0   9   20230531010648  0
0   5   20230531010346  0
0   4   20230531000935  1
0   3   20230531000928  0
0   1   20230531000652  1
Solution

  • This can be done as follows :

    First we identify max time per sensor_serial using group by and max() :

    select sensor_serial, max(time) as max_time
from sensor_data
group by sensor_serial

    Then join this dataset with the table :

    select sd.*
from sensor_data sd
inner join (
  select sensor_serial, max(time) as max_time
  from sensor_data
  group by sensor_serial
) as s on s.sensor_serial = sd.sensor_serial and s.max_time = sd.time

    Demo here