How to retrieve top 2 latest records per device using MySQL

Other Questions MySQL
1

I’m working with a MySQL database that has these columns:

metric_id, machine_name, measurement, recorded_time

I need to get the 2 most recent entries for each unique machine when filtering by a specific metric.

Here’s what I’ve attempted so far:

Attempt 1:

select metric_id, machine_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 2 per machine.

Attempt 2:

select metric_id, machine_name, measurement, recorded_time
from DEVICE_METRICS as a
where a.metric_id=134
  and (select count(*)
       from DEVICE_METRICS as b
       where b.metric_id=a.metric_id
         and b.machine_name=a.machine_name
         and b.recorded_time > a.recorded_time) <=1;

This approach isn’t giving me the correct results either.

What I’m looking for is the 2 most recent timestamps and their corresponding measurements for each individual machine, filtered by a particular metric. Any suggestions would be great!