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

[Jeremy Weiss]

For the sake of TheList archives (and the fact I feel like rambling), let me
step through this a bit:

2009/07/09 - Daniel Burke
> select * from (
> SELECT * FROM news WHERE category = 1 LIMIT 4
> UNION
> SELECT * FROM news WHERE category = 2 LIMIT 3
> UNION
> SELECT * FROM news WHERE category = 6 LIMIT 3
> UNION
> SELECT * FROM news WHERE category = 3 LIMIT 2
> ) order by date desc

Firstly, there's a minor syntax error. As Rudy explained in his Simply SQL
book, "In standard SQL, the UNION query must be given a table alias." Used
as is, MySQL gives the following error:  "Every derived table must have its
own alias." Again, very minor issue.

As others had pointed out, the above doesn't always give the 2 most recent
records. As Ed stated in his email, the ORDER BY applies to the entire
UNION, not the subselects. 

What no one mentioned though, is that it only returns 2 records, not 12. I'm
not sure if this is standard SQL or a MySQL idiosyncrasy. And to make it
even more interesting, the returned records are from the first subselect
statement.

> 2009/07/09 - Jason Handby
> 
> I don't have MySQL to hand, but I'd guess if you change the "TOP" syntax
> to "....LIMIT" then it should work?
> 
 
Correct you are Jason. And now I'm kicking myself as one of my attempts was
oh so close. Here's the MySQL-ized version of Jason's MS SQL query:

SELECT * FROM
 (SELECT * FROM news WHERE category=1 ORDER BY date DESC LIMIT 4) t1
 UNION
 SELECT * FROM
 (SELECT * FROM news WHERE category=2 ORDER BY date DESC LIMIT 3) t2
 UNION
 SELECT * FROM
 (SELECT * FROM news WHERE category=6 ORDER BY date DESC LIMIT 3) t3
 UNION
 SELECT * FROM
 (SELECT * FROM news WHERE category=3 ORDER BY date DESC LIMIT 2) t4
 ORDER BY date DESC

Big thanks to everyone for the help.

-jeremy