SELECT most recent measured value for every sensor

Question

I have two tables in MySQL database - Sensors and Measurements where 'sensor_id' is ID of a sensor which took the measurement, 'value' is measured value and 'measured' is Unix time (when was the value measured). I would like to get last measured value for each sensor from Sensors table, so in this example last 3 rows from Measurements table:

id     sensor_id               value       measured
22     1000000000000001        10.20       1619166847821
23     0100000000000001        67.00       1619166847827
24     0000000000000001        3.05        1619166847830

I've tried somehting like SELECT MAX(m.id) as id, m.sensor_id, m.value, MAX(m.measured) as measured FROM Measurements m JOIN Sensors s ON m.sensor_id=s.id GROUP BY s.id; which works for fine for 'id' and 'measured', but not for 'value':

id     sensor_id               value       measured
22     1000000000000001        23.00       1619166847821
23     0100000000000001        47.00       1619166847827
24     0000000000000001        3.03        1619166847830

Any ideas? Thanks!

You may use window function. Or you may to obtain greatest `measured` per `sensor_id` in subquery and retrieve according rows from another copy of the table. — Akina, Apr 23 '21 at 09:28
Does this answer your question? [Retrieving the last record in each group - MySQL](https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql) — nbk, Apr 23 '21 at 09:37

score 1 · Accepted Answer · answered Apr 23 '21 at 09:35

youerquery fails,because result sets are unordered by definition.

so use for example

CREATE TABLE Measurements  (
  `id` INTEGER,
  `sensor_id` vaRCHAr(16),
  `value` FLOAT,
  `measured` varchar(14)
);

INSERT INTO Measurements 
  (`id`, `sensor_id`, `value`, `measured`)
VALUES
  ('22', '1000000000000001', '10.20', '1619166847821'),
  ('23', '0100000000000001', '67.00', '1619166847827'),
  ('24', '0000000000000001', '3.05', '1619166847830');

SELECT t1.* 
FROM Measurements  t1 
INNER JOIN (SELECT MAX(id) max_id,`sensor_id` FROM Measurements  GROUP BY `sensor_id`) t2
ON t1.`sensor_id` = t2.`sensor_id` AND t1.id = t2.max_id

id | sensor_id        | value | measured     
-: | :--------------- | ----: | :------------
22 | 1000000000000001 |  10.2 | 1619166847821
23 | 0100000000000001 |    67 | 1619166847827
24 | 0000000000000001 |  3.05 | 1619166847830

db<>fiddle here

score 0 · Answer 2 · answered Apr 23 '21 at 09:32

Analytical or WINDOW functions are what you need. I am not sure what version of MySQL they were introduce in so please specify you MySQL version as well.

SELECT DISTINCT
FIRST_VALUE(id) OVER (PARTITION BY sensor_id ORDER BY measured DESC) as id
,sensor_id
,FIRST_VALUE(value) OVER (PARTITION BY sensor_id ORDER BY measured DESC) as value
,FIRST_VALUE(measured ) OVER (PARTITION BY sensor_id ORDER BY measured DESC) as measured 
FROM Measurements m 
JOIN Sensors s ON m.sensor_id=s.id

score 0 · Answer 3 · answered Apr 23 '21 at 11:32

I would like to get last measured value for each sensor from Sensors table

I would just use a correlated subquery:

select m.*
from Measurements m
where s.measured = (select max(m2.measured)
                    from Measurements m2
                    where m2.sensor_id = m.sensor_id
                   );

I don't think sensors is needed for the query.

For performance, you want an index on Measurements(sensor_id, measured).

SELECT most recent measured value for every sensor

3 Answers3