-1

For a Home-Automation Dashboard I am trying to display the latest values for different devices in different rooms. My tables look like this Table: devices

DeviceID Floor Room Measurement More Information
1 0 WC Room-Temp ...
2 0 WC Desired Room-Temp ...
3 0 WC rel. Humidity ...
... .... ... .... ...
15 1 Kid Room-Temp ...
16 1 Kid Desired Room-Temp ...
17 1 Kid rel. Humidity ...

Apart from that I have a table "messdaten" (=measurements) where I store the collected data (DeviceID+Timestamp is unique).

DeviceID Timestamp Value
3 2022-07-28 13:00:03 63
15 2022-07-28 12:58:37 21.3
17 2022-07-28 12:58:37 60
1 2022-07-28 12:55:00 20.8
... ... ...

What I would like to have now is a table in this form where I find all the latest data from each device for one specific room (here for example "WC").

DeviceID Floor Room Measurement Timestamp Value
1 0 WC Room-Temp 2022-07-28 12:55:00 20.8
2 0 WC Desired Room-Temp 2022-07-28 12:53:00 20.5
3 0 WC rel. Humidity 2022-07-28 13:00:03 63

My closest approach so far is:

SELECT devices.DeviceID, Floor, Room, Measurement, max(messdaten.Timestamp), messdaten.Value 
FROM devices LEFT JOIN messdaten ON messdaten.DeviceID = devices.DeviceID 
WHERE   Room = (SELECT Room FROM devices WHERE DeviceID = 1) 
GROUP BY devices.DeviceID 
ORDER BY `devices`.`Measurement` ASC;

Unfortunately using "max" in Timestamp only applies on the column 'Timestamp' so that I get the latest timestamp but not the corresponding value.

zYan
  • 3
  • 3

1 Answers1

0

On MySQL 8+, we can use ROW_NUMBER() here:

WITH cte AS (
    SELECT d.DeviceID, d.Floor, d.Room, d.Measurement, m.Timestamp, m.Value,
           ROW_NUMBER() OVER (PARTITION BY d.DeviceID ORDER BY m.Timestamp DESC) rn
    FROM devices d
    LEFT JOIN messdaten m ON m.DeviceID = d.DeviceID 
    WHERE Room IN (SELECT Room FROM devices WHERE DeviceID = 1) 
)

SELECT DeviceID, Floor, Room, Measurement, Timestamp, Value
FROM cte
WHERE rn = 1
ORDER BY Measurement;
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360