How to get the latest 2 items per category in one select (with mysql)

Question

My data looks like the following:

id|category|insertdate|title....
--------------------------------
1|1|123|test 1
2|1|124|test 2
3|1|125|test 3
4|2|102|test 4
5|2|103|test 5
6|2|104|test 6

What I try to accomplish is get the latest 2 entries per category (as in order by insertdate DESC), so the result should be:

id|....
----
3|....
2|....
6|....
5|....

Getting the latest by using group by is easy, but how do I get the latest 2 without launching multiple queries?

Thanks for any help ;-)
S.

Answer 1

You're not going to be able to do this kind of query in one SELECT statement, but you can wrap it up in one stored procedure that returns one data set by adding results of subqueries to a temporary table for each category, then returning the contents of the temp table.

Pseudocode:

Create a temp table
For each distinct category,
  Add the last two records to the temp table
Return the temp table

You'll end up with the set of data you want in the end, and from the point of view of your application only one query was made.

Answer 2

Another way could be to get an ordered list using group_concat. This wouldn't be any use really if you had a lot of data though.

select group_concat(id order by insertdate desc separator ','), category from tablename group by category

or using sub-selects (this on mysql)

select category,
    (select id from test as test1 where test1.category = test.category order by insertdate desc limit 0,1) as recent1,
    (select id from test as test1 where test1.category = test.category order by insertdate desc limit 1,1) as recent2
from test
group by category;

I know the second option isn't technically one select as there are sub queries however it's the only way I can see to do it.

Answer 3

Here you go buddy!

SET @counter = 0;
SET @category = '';

SELECT
    *
FROM
(
    SELECT
     @counter := IF(data.category = @category, @counter+1, 0) AS counter,
     @category := data.category,
     data.*
    FROM
    (
     SELECT
      *
     FROM test
     ORDER BY category, date DESC
    ) data
) data
HAVING counter < 2

Answer 4

This is a tricky problem in SQL which is best answered by directing you to an excellent in-depth article covering the issue: How to select the first/least/max row per group in SQL. It covers MySQL-specific means of doing this, as well as generic methods.

Answer 5

SELECT * 
FROM category AS c1
WHERE (
    SELECT COUNT(c2.id)
    FROM category AS c2
    WHERE c2.id = c1.id AND c2.insertdate > c1.insertdate
) < 2