[thelist] Q on SQL 'ORDER BY' - part 2

[Tony Crockford]

At 10:06 on Sunday, 11 Apr 2004, jsWalter wrote:

>  ORDER BY contacts.con_title, contacts.con_name;

>
> What I'm wanting to do is sort by NAME, and if NAME is empty, then by  
> TITLE.
>
> Below is my real screwy results.
>
> Is what I'm looking to possible in mySQL?

your current ORDER BY will sort title first then name, but ORDER BY with  
two parameters means sort first by the first paramaeter and if there are  
duplicates then sort by the second parameter.

so if you did:

ORDER BY contacts.con_name, contacts.con_title;

the first rows would be the blank name fields ordered by title, followed  
by names (and only if there are two names the same will title get used to  
order them)

That might be what you're looking for?

But I suspect not

I suspect you want this:

» Director of Marketing and Promotions
» Director of Sales
» E Collman
» Janet Heatherman
» K Olmstead
» Lisa Jackson, Creative Services Manager
» M Carbaugh
» M Zukerman
» P Mutino
» Patrick Ryan
» Scott Shannon
» Lisa Jackson, Creative Services Manager

and I think the only way to do that would be after the select by using  
some sort of scripting language that added the two fields together to  
create a combined "name" and then sort and display on that - of course it  
will be ordering on first name or initial rather than surname....


Maybe MySQL can do that?  I don't know.

If it were me I'd be doing something at data entry to ensure that an index  
field was filled with either surname or job title and then ordering  
results based on that....

hth

Tony