Ordering with NULL

  • Comments posted to this topic are about the item Ordering with NULL

  • At first, I thought this was more of a "did you actually read the code question" than anything else.   I no longer think that because at least one person said the NULL row would be eliminated.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I could have sworn that where the NULL would be in the sorting depended on the collation.

    • This reply was modified 1 month, 2 weeks ago by  Tom_Hogan.
  • The ANSI/ISO standards specify an extra clause that goes after the order of the sort (ASC | DESC) which will let you decide the order of the nulls within that column (NULL FIRST | NULL LAST). Perhaps Microsoft will catch up in the future.

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • jcelko212 32090 wrote:

    The ANSI/ISO standards specify an extra clause that goes after the order of the sort (ASC | DESC) which will let you decide the order of the nulls within that column (NULL FIRST | NULL LAST). Perhaps Microsoft will catch up in the future.

    Heh... but ordering in a database shouldn't matter, right Joe!? 😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • This was removed by the editor as SPAM

  • JC makes a good point, the ANSI/ISO has the extra clause of (NULL FIRST | NULL LAST), unfortunately, omitted by many vendors including MS.

    😎

    In SQL Server, NULL FIRST is implied, NULL LAST means using ISNULL and the highest value as a replacement, bit of a bonkers approach.

  • Steve, you're not the chap I thought you were.

    Where's the Fuller's Golden Honeydew, and then where was the Guiness. (Or, have you drunk them all?).

    Kenneth Spencer

    You never know: reading my book: "All about your computer" might just tell you something you never knew!
    lulu.com/kaspencer

  • kaspencer wrote:

    Steve, you're not the chap I thought you were.

    Where's the Fuller's Golden Honeydew, and then where was the Guiness. (Or, have you drunk them all?).

    Kenneth Spencer

    Why go for the Golden Honeydew when you can have ESB

    😎

    Have more than a handful and ESB becomes Extra SQL Blunder 😉

  • 133ff124bb6fbd093227e994c3564880

     

    I grabbed the first few names I thought of from a visit to a local restaurant. I have had these, and I like some of them. I tend to be an Amber or Brown ale guy, but a nice Guinness while in Dublin is always welcome.  I have tried the Golden Honeydew, but I'll add it to a list if I ever get back over to the UK  and they let me in.

    Here in Denver, we have a lot of local craft brews. This is one of my favorites, https://www.105westbrewing.com, though unfortunately I can't keep Deez Nuts in stock at the house. My daughter and son will drink them up.

  • The password for getting into the UK is SQLServerCentral!

    My kids (they are both 40-something now) have to buy their own!

    Best wishes,

    Ken.

    You never know: reading my book: "All about your computer" might just tell you something you never knew!
    lulu.com/kaspencer

  • jcelko212 32090 wrote:

    The ANSI/ISO standards specify an extra clause that goes after the order of the sort (ASC | DESC) which will let you decide the order of the nulls within that column (NULL FIRST | NULL LAST). Perhaps Microsoft will catch up in the future.

    Don't see much of a point.

    ORDER BY CASE WHEN Col IS NULL THEN 0 ELSE 1 END, Col

    Switch 0 and 1 and you get your NULLS wherever you want them

  • The case expression returns a value of a particular data type. In your example, you're sorting on a numeric, without regard to what the original column was. This would seem to have an awful lot of overhead as well as possible incorrect values and a different correlation if the original column was a character data type.. But just from an aesthetic viewpoint, this is a kludge to get around the lack of a standard feature.

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • Sergiy wrote:

    jcelko212 32090 wrote:

    The ANSI/ISO standards specify an extra clause that goes after the order of the sort (ASC | DESC) which will let you decide the order of the nulls within that column (NULL FIRST | NULL LAST). Perhaps Microsoft will catch up in the future.

    Don't see much of a point.

    ORDER BY CASE WHEN Col IS NULL THEN 0 ELSE 1 END, Col

    Switch 0 and 1 and you get your NULLS wherever you want them

    The case statement needs a value, NULL is not a value, it is the absence of a value.

    😎

    Substituting non-value entries to "get the right results" is altering the data!

  • jcelko212 32090 wrote:

    The case expression returns a value of a particular data type. In your example, you're sorting on a numeric, without regard to what the original column was. This would seem to have an awful lot of overhead as well as possible incorrect values and a different correlation if the original column was a character data type.

    what???

    Are you sure about it?

    Can you elaborate on it please? I cannot see your case.

    But just from an aesthetic viewpoint, this is a kludge to get around the lack of a standard feature.

    I never heard of any art excibition showing new (or traditional) trends in SQL coding. I don't think people would appreciate the aestetics of it anyway.

    As for myself - I'm trying my best to make the code working with no errors, and with no performance degradation, so neither me nor anyone else would need to open it again, and no aesthetic confrontations would come from it.

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply