[thelist] Q on SQL 'ORDER BY' - part 2
Tony Crockford
tonyc at boldfish.co.uk
Sun Apr 11 04:46:36 CDT 2004
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
More information about the thelist
mailing list