ORDER BY multiple columns

[javagirl]

I may have designed my table wrong, but I have a table with both organization and individual names. I would like for the table to sort by orgname AND individual_lname combined. Is there anyway to combine two columns for an ORDER BY?

Something like this:
ABC Company << orgname
Barry Baker << individual_lname
Dover Motors << orgname
Susan Green << individual_lname

Not this:
ABC Company << orgname
Dover Motors << orgname
Barry Baker << individual_lname
Susan Green << individual_lname

Thanks.

 

[fluteplr]

Post the structure of the table(s) without that it is impossible to figure out what the problem is.

 

[dnoeth]

Do you have a table with two columns orgname and individual_lname and they may be NULL?
If orgname is NULL then use individual_lname instead?

order by
coalesce(orgname, individual_lname)

If your DBMS doesn't support coalesce, look for something similar like IF.

Dieter

 

[javagirl]

COALESCE in the ORDER BY isn't working for me (yet) in MySQL.

SELECT id, lname, fname, orgname, affiliation, url, comment FROM endorsers ORDER BY COALESCE(orgname, lname), fname; << Yields list with all orgnames in sort order after sorted individual names.

Here is table def:

CREATE TABLE endorsers (
id int(11) NOT NULL auto_increment,
lname text NOT NULL,
fname text NOT NULL,
orgname text,
affiliation enum('Individual','Organization') NOT NULL,
...

lname can never be NULL, but orgname may be NULL.

Thanks for your help. I would never have thought of using COALESCE. Back to the books!

 

[tdatgod]

hi,
if you are trying to get 1 column projected from 2 coulmns could you use a derived table which is a union of the 2 fields into a single field?

sel b.myname,b.affiliation
from
( (sel orgname as myname, affiliation from
endorsers
where affliation = 'Organization'

union

select trim (firstname) || ' ' || trim(lastname) as myname, affiliation from
endorsers
where affliation = 'Individual'

) as b )

order by b.myname;

 

[javagirl]

Yes, UNION sounds good, but alas, my web host company has just upgraded to MySQL 3.23. UNION support starts in 4.0. I think I will have to make do with separate queries and separate the org from the individual listings.