[thelist] can / should this be done in SQL?

[Jeremy Weiss]

Working with PHP/MySQL

I have a query that pulls the 12 most recent records from a news table. The
client has decided that too many of those records are of one type so she now
wants it to pull: 
4 most recent records from category 1
3 most recent records from category 2
3 most recent records from category 3
2 most recent records from category 4

Now, I could just run 4 queries and then use PHP to merge the results into
an array and sort the array based on date. But I'm curious if there's a way
to do this in a single SQL query that I'm overlooking.

I tried:

SELECT * FROM news WHERE category = 1 ORDER BY date DESC LIMIT 4
UNION
SELECT * FROM news WHERE category = 2 ORDER BY date DESC LIMIT 3
UNION
SELECT * FROM news WHERE category = 6 ORDER BY date DESC LIMIT 3
UNION
SELECT * FROM news WHERE category = 3 ORDER BY date DESC LIMIT 2

Which gives me an error 'Incorrect usage of UNION and ORDER BY' as it
should. I've also tried various versions of subselects to no avail.

Again, this one's not mission critical, just trying to push my SQL limits a
bit.

-jeremy