Re: [PHP-DB] 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/3288921 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
Re: [PHP-DB] sql statement - complex order by
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, [EMAIL PROTECTED] <[EMAIL PROTECTED]> 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... [EMAIL PROTECTED] 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: [PHP-DB] 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... [EMAIL PROTECTED] 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: [PHP-DB] 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... [EMAIL PROTECTED] 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: [PHP-DB] 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... [EMAIL PROTECTED] 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: [PHP-DB] 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: [PHP-DB] 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
[PHP-DB] 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
