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

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.