[thelist] can / should this be done in SQL?
Jason Handby
jason.handby at corestar.co.uk
Thu Jul 9 02:12:43 CDT 2009
> 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
More information about the thelist
mailing list