sql statement - complex order by

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

Re: sql statement - complex order by

Bryan wrote:
> 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...

select * from productgroup where groupid = $productid order by (label =
'Cats') desc, title

And I do hope you're properly validating and escaping $productid.

-Stut

--
http://stut.net/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: sql statement - complex order by

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/en/sorting-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.

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: sql statement - complex order by

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/en/sorting-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

Re: sql statement - complex order by

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/en/sorting-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

Re: sql statement - complex order by

Yeah, that's a bit of an important piece of information. Some tricks do work across versions of SQL, but not always.

Something else you can try is creating an artificial column to sort by. Excuse the code, it's been ages since I've worked with MS SQL so syntax is probably off, but just to demonstrate the point:

SELECT * FROM productgroup WHERE groupid = $productid
ORDER BY IIF(label = 'Cats', 0, 1), title

Was that MS SQL or just Access that used IIF? (immediate IF.. wtf is an 'immediate IF' anyway?)

-TG


= = = Original message = = =

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/en/sorting-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.

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: sql statement - complex order by

------=_Part_116013_27810331.1183427367976
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

How about a union?

SELECT * FROM productgroup WHERE groupid = $productid
AND label = 'Cats' ORDER BY title
UNION
SELECT * FROM productgroup WHERE groupid = $productid
AND label != 'Cats' ORDER BY label,title

Also, for long-term maintenance, it would probably be better to list the
columns rather than SELECT *. Someone adds one column to your table and
whamo. Things break.

Bonne chance.


On 7/2/07, tg-php@gryffyndevelopment.com
wrote:
>
> Yeah, that's a bit of an important piece of information. Some tricks do
> work across versions of SQL, but not always.
>
> Something else you can try is creating an artificial column to sort
> by. Excuse the code, it's been ages since I've worked with MS SQL so syntax
> is probably off, but just to demonstrate the point:
>
> SELECT * FROM productgroup WHERE groupid = $productid
> ORDER BY IIF(label = 'Cats', 0, 1), title
>
> Was that MS SQL or just Access that used IIF? (immediate IF.. wtf is an
> 'immediate IF' anyway?)
>
> -TG
>
>
> = = = Original message = = =
>
> 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/en/sorting-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.
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

------=_Part_116013_27810331.1183427367976--

Re: sql statement - complex order by

Bryan wrote:
> 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?

select *, case when label='cats' then 1 else 2 end as order_by where
groupid=$productid order by order_by, title;

See http://www.databasejournal.com/features/mssql/article.php/32 88921
for a better example.

--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php