Home > Archive > PHP DB > July 2007 > Re: [PHP-DB] sql statement - complex order by
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Re: [PHP-DB] sql statement - complex order by
|
|
| tg-php@gryffyndevelopment.com 2007-07-02, 6:58 pm |
| Try this:
SELECT * FROM productgroup WHERE groupid = $productid
ORDER BY label = 'Cats' DESC, title
The test SQL I did to make sure I understood it was this (against our Users table):
select * from users order by first = 'Bob' DESC, first, last
It put all the "Bob"s first, sorting them by first/last, then put everyone else after the "Bob"s sorted by first/last.
If you don't put DESC on the 'Cats', it looks like it'll put the 'Cats' at the bottom of the list.
Also refer to the user comments here:
http://dev.mysql.com/doc/refman/4.1...rting-rows.html
good luck!
-TG
= = = Original message = = =
SELECT * FROM productgroup WHERE groupid = $productid
AND label = 'Cats' ORDER BY title
SELECT * FROM productgroup WHERE groupid = $productid
AND label != 'Cats' ORDER BY label,title
I'd like to find a way to combine these 2 statements. I want to list out
all the products, ordered by title but listing out all the Cats products
first. Any way to do that without having separate statements?
Thanks...
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
________________________________________
___________________
Sent by ePrompter, the premier email notification software.
Free download at http://www.ePrompter.com.
| |
|
| I think there's one small piece of data I left out. I'm working with
php/mssql, no mysql. I'll move to mysql when I get everything else
built. Mssql 2000 doesn't seem to like the = sign in the order by
clause. It looks like both of you so far have come up with the same
syntax though so it must work on mysql. ;-)
Thanks guys...
tg-php@gryffyndevelopment.com wrote:
> Try this:
>
> SELECT * FROM productgroup WHERE groupid = $productid
> ORDER BY label = 'Cats' DESC, title
>
> The test SQL I did to make sure I understood it was this (against our Users table):
>
> select * from users order by first = 'Bob' DESC, first, last
>
> It put all the "Bob"s first, sorting them by first/last, then put everyone else after the "Bob"s sorted by first/last.
>
> If you don't put DESC on the 'Cats', it looks like it'll put the 'Cats' at the bottom of the list.
>
> Also refer to the user comments here:
> http://dev.mysql.com/doc/refman/4.1...rting-rows.html
>
> good luck!
>
> -TG
>
> = = = Original message = = =
>
> SELECT * FROM productgroup WHERE groupid = $productid
> AND label = 'Cats' ORDER BY title
>
> SELECT * FROM productgroup WHERE groupid = $productid
> AND label != 'Cats' ORDER BY label,title
>
> I'd like to find a way to combine these 2 statements. I want to list out
> all the products, ordered by title but listing out all the Cats products
> first. Any way to do that without having separate statements?
>
> Thanks...
>
| |
|
| I think there's one small piece of data I left out. I'm working with
php/mssql, not mysql. I'll move to mysql when I get everything else
built. Mssql 2000 doesn't seem to like the = sign in the order by
clause. It looks like both of you so far have come up with the same
syntax though so it must work on mysql. ;-)
Thanks guys...
tg-php@gryffyndevelopment.com wrote:
> Try this:
>
> SELECT * FROM productgroup WHERE groupid = $productid
> ORDER BY label = 'Cats' DESC, title
>
> The test SQL I did to make sure I understood it was this (against our Users table):
>
> select * from users order by first = 'Bob' DESC, first, last
>
> It put all the "Bob"s first, sorting them by first/last, then put everyone else after the "Bob"s sorted by first/last.
>
> If you don't put DESC on the 'Cats', it looks like it'll put the 'Cats' at the bottom of the list.
>
> Also refer to the user comments here:
> http://dev.mysql.com/doc/refman/4.1...rting-rows.html
>
> good luck!
>
> -TG
>
> = = = Original message = = =
>
> SELECT * FROM productgroup WHERE groupid = $productid
> AND label = 'Cats' ORDER BY title
>
> SELECT * FROM productgroup WHERE groupid = $productid
> AND label != 'Cats' ORDER BY label,title
>
> I'd like to find a way to combine these 2 statements. I want to list out
> all the products, ordered by title but listing out all the Cats products
> first. Any way to do that without having separate statements?
>
> Thanks...
>
| |
|
|
|
|
|
|
|