Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2002
    Posts
    189

    Unanswered: Ordering with NULL values in a column

    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?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    ORDER 
        BY CASE WHEN idContacts IS NULL
                THEN 937 END
         , lastName
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ..before you ask 937 is a magic number to Rudy (R937), it could be any number....
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jan 2002
    Posts
    189
    That's awesome. Nice one. I have kept it as 937 in your honour

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I often wonder how much production code has 937 spuriously in it.

    I know of a few chunks in prod at our place
    George
    Home | Blog

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •