Page 1 of 1
sorting problem
Posted: Sun Mar 30, 2003 8:45 am
by leebo
I have a database where it stores brand names:
Adidas
Puma
Nike
Reebok
Diadora
the column name is brand
How can I display the brand name in order of the names ?
click on adidas it will list all items but adidas first
click on puma it will list all items but puma first
Can this be done ? I know it can be done by listing just adidas or puma etc but i need all records displaying but in order of brand name ?
Any ideas ??
Posted: Mon Mar 31, 2003 12:01 am
by fractalvibes
Add a simple Order By Brand to the SQL to display the names alphabetically.
Posted: Mon Mar 31, 2003 10:53 am
by leebo
Well if you read my question you will notice that i dont want to do that - I`m wanting to sort depending on the brand name.
click on adidas it will display ALL records starting with adidas
click on puma it will display ALL records starting with puma
click on nike it will display ALL records starting with nike
not
select * from table order by brand

Posted: Mon Mar 31, 2003 11:43 am
by Rob the R
I would tend to approach this with two different selects: first for the brand selected and second for everything except the brand selected. I'm not sure what the UNION operator does to the sort order, but you could try:
Code: Select all
select * from table
where brand = 'Puma'
UNION
select * from table
where brand != 'Puma' ;
but I expect the following would work even if the above doesn't:
Code: Select all
(select '1' sort_order, * from table
where brand = 'Puma')
UNION
(select '2' sort_order, * from table
where brand != 'Puma')
order by sort_order ;
Posted: Mon Mar 31, 2003 3:07 pm
by Gen-ik
or just a simple SELECT * FROM table WHERE `brand`='Puma' will work.
Posted: Mon Mar 31, 2003 3:48 pm
by leebo
Yes but SELECT * FROM table WHERE `brand`='Puma' will only list puma !!!!!!!!
I want to list all records starting with puma including adidas , nike, reebok etc...

Posted: Mon Mar 31, 2003 4:01 pm
by hedge
select brand, name from table order by brand,name
Posted: Mon Mar 31, 2003 8:54 pm
by spammich
I think Rob_R has the right idea, but couldn't you also just do two seperate queries?
select * from shoes where brand="puma"
list them all out and then do
select * from shoes where brand != "puma"
and then list them all out.
The UNION is definatly more elegent, but this would work too.
Posted: Tue Apr 01, 2003 2:14 am
by twigletmac
The only problem with UNION is that if you're using MySQL it's only been implemented since version 4:
http://www.mysql.com/doc/en/UNION.html
If you're using MySQL version 3 then this may be of interest:
http://www.nstep.net/~mpbailey/programm ... .union.php
But otherwise, the simple solution is to do two SELECTS as spammich stated and put the ORDER BY statement in the second one:
Code: Select all
SELECT field1, field2, field3 FROM table WHERE brand='puma'
then
Code: Select all
SELECT field1, field2, field3 FROM table WHERE brand <> 'puma' ORDER BY brand
and if you're using a database that can do UNIONs then you can just do that.
Mac