How to get the last 2 elements for each category in one select (with mysql)

Question

My data is as follows:

  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'm trying to do is get the last 2 entries for each category (as in order with insertdate DESC), so the result should be:

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

Getting the latter with group by very simple, but how do I get the last 2 without running multiple queries?

Thanks for any help ;-)
S.

Answer 1

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 2

This is a complex SQL issue that is best answered by directing you to an excellent in-depth article on the issue: How to choose the first / smallest / max for each group in SQL . It covers MySQL-specific tools for this, as well as general methods.

Answer 3

You cannot make such a query in a single SELECT statement, but you can wrap it in a single stored procedure that returns one data set, adding the results of subqueries to a temporary table for each category, then returns 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 

At the end, you will get the dataset that you want at the end, and from the point of view of your application, only one request has been made.

Answer 4

Another way could be to get an ordered list using group_concat. It would be useless if you had a lot of data.

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

or using subsamples (this is in 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 that the second option is not technically selected, since there are sub-queries, but this is the only way I can do this.

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