CODEWITHSUNDEEP
phpsqlmysqlselectsql-order-by
Misha Moroshko
Misha Moroshko

Reputation: 171509

Need help with SQL ORDER BY

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 ?

Upvotes: 2

Views: 80

Answers (3)

Pekka
Pekka

Reputation: 449823

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

Upvotes: 2

Beth
Beth

Reputation: 4670

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

Upvotes: 1

SQLMenace
SQLMenace

Reputation: 135181

Try

ORDER BY COALESCE(name,default_name)

Upvotes: 5

Related Questions