sorting problem

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
leebo
Forum Commoner
Posts: 44
Joined: Sun Oct 20, 2002 9:49 am

sorting problem

Post 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 ??
fractalvibes
Forum Contributor
Posts: 335
Joined: Thu Sep 26, 2002 6:14 pm
Location: Waco, Texas

Post by fractalvibes »

Add a simple Order By Brand to the SQL to display the names alphabetically.
leebo
Forum Commoner
Posts: 44
Joined: Sun Oct 20, 2002 9:49 am

Post 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

:roll:
Rob the R
Forum Contributor
Posts: 128
Joined: Wed Nov 06, 2002 2:25 pm
Location: Houston

Post 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 ;
Gen-ik
DevNet Resident
Posts: 1059
Joined: Mon Aug 12, 2002 7:08 pm
Location: London. UK.

Post by Gen-ik »

or just a simple SELECT * FROM table WHERE `brand`='Puma' will work.
leebo
Forum Commoner
Posts: 44
Joined: Sun Oct 20, 2002 9:49 am

Post 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...
:roll:
hedge
Forum Contributor
Posts: 234
Joined: Fri Aug 30, 2002 10:19 am
Location: Calgary, AB, Canada

Post by hedge »

select brand, name from table order by brand,name
spammich
Forum Newbie
Posts: 11
Joined: Sun Mar 23, 2003 12:40 am

Post 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.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
Post Reply