I need to get the 2 most recent records (based on recorded_time) along with their measurement values for each unique equipment, filtered by a specific metric.
Here’s what I’ve attempted so far:
Approach 1:
select metric_id, equipment_name, measurement, recorded_time
from DEVICE_METRICS
where metric_id=25
order by recorded_time DESC limit 2;
This only returns the overall top 2 records, not grouped by equipment.
Approach 2:
select metric_id, equipment_name, measurement, recorded_time
from DEVICE_METRICS as a
where a.metric_id=156
and (select count(*)
from DEVICE_METRICS as b
where b.metric_id=a.metric_id
and b.equipment_name=a.equipment_name
and b.recorded_time > a.recorded_time) <=1;
This approach isn’t giving me the expected results either.
What I’m looking for is the 2 most recent timestamp entries with their corresponding measurement values for each individual equipment, filtered by a particular metric ID. Any suggestions would be great!
Your second approach was close, but the comparison operator’s wrong. The correlated subquery counts records with timestamps greater than the current row, so for the top 2 records you want count <= 1 (0 for newest, 1 for second newest). You should use >= in the timestamp comparison though, not >.
SELECT metric_id, equipment_name, measurement, recorded_time
FROM DEVICE_METRICS a
WHERE a.metric_id = 156
AND (SELECT COUNT(*)
FROM DEVICE_METRICS b
WHERE b.metric_id = a.metric_id
AND b.equipment_name = a.equipment_name
AND b.recorded_time >= a.recorded_time) <= 2
ORDER BY equipment_name, recorded_time DESC;
This works on older MySQL versions without window functions. The subquery counts how many records have the same or newer timestamps for each equipment, keeping only the top 2.
you could also try rank() instead of row_number() if u need different tie handling. just make sure your mysql version supports window funcs - older ones don’t, so u’d have to use the correlated subquery method. but fix that condition to >= instead of >.
You could also try a self-join with proper filtering. I’ve seen this run faster than correlated subqueries on some MySQL setups, especially with good indexes.
SELECT DISTINCT d1.metric_id, d1.equipment_name, d1.measurement, d1.recorded_time
FROM DEVICE_METRICS d1
LEFT JOIN DEVICE_METRICS d2 ON d1.equipment_name = d2.equipment_name
AND d1.metric_id = d2.metric_id
AND d1.recorded_time < d2.recorded_time
WHERE d1.metric_id = 25
GROUP BY d1.metric_id, d1.equipment_name, d1.measurement, d1.recorded_time
HAVING COUNT(d2.recorded_time) < 2
ORDER BY d1.equipment_name, d1.recorded_time DESC;
Basically, it joins each record with newer ones from the same equipment. Keep records with less than 2 newer timestamps and you get your top 2. Works across MySQL versions and I’ve used it plenty when window functions weren’t an option.
Use window functions - they’re perfect for this. ROW_NUMBER() assigns sequential numbers to rows within each equipment partition, sorted by recorded_time descending. Way cleaner than correlated subqueries and performs better with large datasets.
SELECT metric_id, equipment_name, measurement, recorded_time
FROM (
SELECT metric_id, equipment_name, measurement, recorded_time,
ROW_NUMBER() OVER (PARTITION BY equipment_name ORDER BY recorded_time DESC) as rn
FROM DEVICE_METRICS
WHERE metric_id = 25
) ranked
WHERE rn <= 2;
I’ve run this pattern tons of times in production and it handles edge cases well. Inner query ranks all records per equipment, outer query keeps just the top 2. Don’t forget an index on (metric_id, equipment_name, recorded_time) for best performance.