'MySQL Query the most recent 10 records per different ID
I'm trying to retrieve through a MySQL query, the last 2 records (Chronologically from our dt (date/time) column) of each ID. There can be multiple records with same server ID. My code so far is, but not working:
select * from table
where sid = 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 table
group by 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.
Solution 1:[1]
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.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|---|
Solution 1 | garethhumphriesgkc |