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

[Jason Handby]

> 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

Unfortunately this won't do what Jeremy wants, as the records selected
for each category may not be the most recent ones.

In MSSQL you'd do this:

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

I don't have MySQL to hand, but I'd guess if you change the "TOP" syntax
to "....LIMIT" then it should work?


> * MySQL may or may not support this kind of stuff, but I still
> remember when it didn't do transactions, my opinion is tainted.

I'm looking forward to an inbox full of MySQL evangelism today then ;-)



Jason