MySQL Query the most recent 10 records per different ID-CodePudding

I'm trying to retrieve through a MySQL query, the last 2 records (Chronologically from our dt (date/time) column) of each Server ID. There can be multiple records with same server ID. My code so far is, but not working:

select * from table
where server_id = server_id
Order by dt Desc limit 2"

I have also tried using the ALL function however, my MySQL isn't updated enough for that, and I can't currently update it so that is not an option. I have also tried:

select * (partition by server_id order by dt desc) from table
limit 2;

What I feel like is the closest to solving this so far is this code I have generated:

select * from monitor_data
group by server_id
Order by dt Desc limit 10
;

This issue with this code above is its only querying 1 entry per ID now instead of 10.

CodePudding user response：

Quick and dirty - use a subquery to get the rows you're interested in, and join to it:

select * from table join (select dt, server_id, count(*) from table
group by dt, server_id
Order by dt Desc limit 2) tops on table.server_id=tops.server_id and table.dt=tops.dt

Performance won't be great, but might be sufficient for your needs.