MySQL groupwise limit query

Question

Say I've got a table:

create table foo (
  corp_id int not null,
  tdate date not null,
  sec_id int unsigned not null,
  amount int unsigned not null,
  primary key (corp_id, sec_id, tdate)
);

The following query will return the sum of the amount column for all corp_id's and dates:

select corp_id, tdate, sum(amount) from foo group by corp_id, tdate;

How can I now limit this query to return only the top 5 latest dates per corp_id?

Answer 1

You can use a subquery to determine what the fifth date was for each corp_id:

select
    corp_id,
    tdate,
    sum(amount)
from
    foo f
where
    tdate >= 
         (select tdate 
          from foo 
          where corp_id = f.corp_id 
          order by tdate desc 
          limit 1 offset 4)

The limit 1 offset 4 means that you go to the fifth record of the query, and then pick just one row. Check out the MySQL docs for more on LIMIT and OFFSET.