MySQL limit results per column

[EdP21]

Say I have the following data in a table:

date_added category

15/10/2009 Novels

12/10/2009 Novels

12/10/2009 Novels

11/10/2009 Novels

17/10/2009 Films

16/10/2009 Films

15/10/2009 Films

15/10/2009 Films

etc.

I want a SQL query to return just the first (say) 2 rows in each Category, i.e.

15/10/2009 Novels

12/10/2009 Novels

17/10/2009 Films

16/10/2009 Films

I could use a LIMIT on the whole result set, but that's not what I need here, and wondered if anyone could help. Of course, I could use PHP to loop through the whole table and ignore all but the first 2 rows for each Category but would prefer a more elegant solution.

Ed

[David_Powers]

Use UNION.

(SELECT date_added, category FROM myTable WHERE category = 'Novels' LIMIT 2)
UNION
(SELECT date_added, category from myTable WHERE category = 'Films; LIMIT 2)
ORDER BY category, date_added

[EdP21]

Thanks David

I might have to do that (and should have been clearer) but there will be more categories so I was hoping for a more generic solution. If there isn't I can always use PHP to build the set of UNION queries. It's just not very neat!

Ed

[bregent]

What you are trying to do is get the top 'N' values per group. See if either of these two methods helps:

http://support.microsoft.com/kb/210039

[bregent]

Here's a few more solutions:

http://www.sql-ex.ru/help/select16.php