Need help with SQL ORDER BY

Question

I have a table of contacts which has name and default_name columns among others.

Contact name is name, unless name=NULL and then it is default_name (which cannot be NULL).

I would like to order my selection by contact name.

For example:

contact_id          name          default_name
----------          ----          ------------
    1               Dave             David
    2                                Misha
    3                                Alex
    4            Brother Leon        Leon

should become:

contact_id          name          default_name
----------          ----          ------------
    3                                Alex
    4            Brother Leon        Leon
    1               Dave             David
    2                                Misha

How would I achieve this ?

Answer 1

Try

ORDER BY COALESCE(name,default_name)

Answer 2

This will work:

ORDER BY (IF(name IS NOT NULL, name, default_name))

SQLMenace's solution is better. Leaving this in place to show how to use IF() in ORDER clauses.

mySQL Reference: Control Flow Structures

Answer 3

details differ depending on the DBMS, but the general idea is to ORDER BY IFNULL(name, default_name) for appropriate choice of the function IFNULL. The semantics of IFNULL are that it's the first value unless it's null, and then it's the second value. Most DBMSes know it by about that name.

http://dev.mysql.com/doc/refman/4.1/en/control-flow-functions.html#function_ifnull