mysql - Sorting groups of data looking at the newest entry

Question

I have a table with two columns team and date. The date column has the date that the entry was added to the table.

I want to print the last 10 entries of each team sorted by date DESC. I also want to sort these groups of team entries by date DESC.

I tried a lot of things, but with no luck. It worked, but with 2 queries which is not acceptable in this case.

How can I do this with a single query? I have the feeling that this is a really newbie question.

Answer 1

SELECT rows.team, rows.date FROM (
  SELECT team, date,
    IF( @prev <> team, @rownum := 1, @rownum := @rownum+1 ) AS rownum,
    @prev := team
  FROM my_table
  JOIN (SELECT @rownum := NULL, @prev := 0) AS init
  ORDER BY team, date DESC
) AS rows
WHERE rownum <= 10

We make a temporary (virtual) table in the sub-query with rows ordered by team, date DESC and we start from the top giving an incrementing row number to each row and whenever team changes we reset the row number, then in the outer query we filter out any row that has row number greater than 10.