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

Daniel Burke dan.p.burke at gmail.com
Thu Jul 9 01:30:04 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

If I was planning on supporting a poor imlementation of SQL, for
example MySQL*, I would do this in PHP. Otherwise SQL all the way.

Not a good way to do it if you're planning of having this scale to
multiple servers, although at that point you will know better ways to
do it.

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

regards,

dan
--
"It's your privilege as an artist to inflict the pain of creativity on
yourself." --Programming Perl 3rd Edition, end of first chapter.



On Thu, Jul 9, 2009 at 2:31 PM, Jeremy Weiss<eccentric.one at gmail.com> wrote:
> 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
>
>
> --
>
> * * Please support the community that supports you.  * *
> http://evolt.org/help_support_evolt/
>
> For unsubscribe and other options, including the Tip Harvester
> and archives of thelist go to: http://lists.evolt.org
> Workers of the Web, evolt !
>



More information about the thelist mailing list