Ordering with NULL values in a column

Spudhead · #1 (**permalink**) 06-01-09, 11:43

I'm trying to order the result of joining my Users table to my UserContacts table.

I get three columns:
idContacts
firstName
lastName

the field idContacts may or may not be NULL.

If idContacts is NULL, I want them to appear at the end of the list.

I tried doing:

Code:

ORDER BY idContacts DESC, lastName

But the first ordering rule screws with the second one - it does now list people who do have an idContacts first, but it orders them by idContacts rather than lastName.

I want to shove all the people without an idContacts to the bottom of the list, and then order by lastName.

Is this possible?

r937 · #2 (**permalink**) 06-01-09, 11:47

Code:

ORDER 
    BY CASE WHEN idContacts IS NULL
            THEN 937 END
     , lastName

healdem · #3 (**permalink**) 06-01-09, 11:54

..before you ask 937 is a magic number to Rudy (R937), it could be any number....

Spudhead · #4 (**permalink**) 06-01-09, 13:10

That's awesome. Nice one. I have kept it as 937 in your honour

gvee · #5 (**permalink**) 06-01-09, 19:30

I often wonder how much production code has 937 spuriously in it.

I know of a few chunks in prod at our place

r937 · #6 (**permalink**) 06-01-09, 22:44

i have some sql with the stooges --

Code:

ORDER
    BY CASE WHEN condition1 THEN 'Curly'
            WHEN condition2 THEN 'Larry'
            WHEN condition3 THEN 'Moe'
            ELSE 'Shemp' END

Internet Services

Web Development

Digital Marketing

Consumer Tech