mysql - SQL Query for current state of all entities in event table

Question

I have a table that contains event data for N number of devices, each of which may or may not be adding data to the table at a given time. I need to query the table for the last status of each distinct device in table, as well as the prior status of the device.

Example table data:

id   | device    | event_data  | date_time
________________________________________________
1019 | sensor501 | open        | 2018-01-05T00:05:15Z
1020 | sensor509 | closed      | 2018-01-05T00:05:16Z
1021 | sensor501 | reset       | 2018-01-05T00:05:18Z
1022 | sensor501 | closed      | 2018-01-05T00:05:19Z
1023 | sensor974 | open        | 2018-01-05T00:05:27Z
1024 | sensor729 | closed      | 2018-01-05T00:06:07Z
1025 | sensor729 | reset       | 2018-01-05T00:06:46Z

Example result data:

device    | event_data  | prev_event_data | date_time
________________________________________________
sensor501 | closed      | reset           | 2018-01-05T00:05:19Z
sensor509 | closed      | null            | 2018-01-05T00:05:16Z
sensor974 | open        | null            | 2018-01-05T00:05:27Z
sensor729 | reset       | closed          | 2018-01-05T00:06:46Z

How would you, in the most efficient way, query the example table to get the example result data?

Answer 1

This can be done with correlated sub-queries. First to get the latest event_data and then one more to get the previous row of the latest.

select t1.device,t1.event_data,t1.date_time,
(select t2.event_data from tbl t2 
 where t1.device=t2.device and t1.date_time>t2.date_time 
 order by t2.date_time desc limit 1) as prev_event_data
from tbl t1
where t1.date_time = (select max(t2.date_time)
                      from tbl t2
                      where t1.device=t2.device
                     )

In case you switch over to a dbms version that supports window functions, use

select device,event_data,prev_event_data,date_time
from (select t.*
      ,lead(event_data) over(partition by device order by date_time) as next_event_data
      ,lag(event_data) over(partition by device order by date_time) as prev_event_data
      from tbl t
     ) t 
where next_event_data is null -- last row in the group which will be the row with latest date_time