ORDER BY ASC and NULLs last

  • Horia Negrila

    SSC Veteran

    Points: 252

    Comments posted to this topic are about the item ORDER BY ASC and NULLs last

  • This was removed by the editor as SPAM

  • paul s-306273

    SSChampion

    Points: 10615

    Tricky.

  • Carlo Romagnano

    SSC-Insane

    Points: 21876

    Stewart "Arturius" Campbell (4/6/2016)


    Interesting, never thought of doing it that way

    Nice question, thanks Horia

    +1

  • g.maxfield

    Default port

    Points: 1471

    It also works using Order by val desc which is what I selected so I got an error on the correct answer

  • Mighty

    SSCrazy Eights

    Points: 8559

    g.maxfield (4/7/2016)


    It also works using Order by val desc which is what I selected so I got an error on the correct answer

    That is not correct what you say. The ORDER BY DESC will return 1, 0, -1, NULL, and not -1, 0, 1, NULL.

  • g.maxfield

    Default port

    Points: 1471

    OK Noted. Thanks 😎

  • Ed Wagner

    SSC Guru

    Points: 286969

    Carlo Romagnano (4/7/2016)


    Stewart "Arturius" Campbell (4/6/2016)


    Interesting, never thought of doing it that way

    Nice question, thanks Horia

    +1

    +1 as well. Very cool.

  • George Vobr

    SSCrazy Eights

    Points: 9085

    Thanks Horia, interesting question and good explanation. It may be useful when dealing with NULLs.

  • Hugo Kornelis

    SSC Guru

    Points: 64675

    Stewart "Arturius" Campbell (4/6/2016)


    Interesting, never thought of doing it that way

    Nice question, thanks Horia

    Nor should you. In production code, I would always write out the explicit intent instead of relying on such a trick.

    ORDER BY CASE WHEN val IS NULL THEN 2 ELSE 1 END, -- Ensure that NULL sorts last

    val -- Within non-NULL, order by ascending value


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Carlo Romagnano

    SSC-Insane

    Points: 21876

    The correct answer works with numeric column, but it doesn't with other types.

  • sknox

    SSChampion

    Points: 12284

    Hugo Kornelis (4/7/2016)


    Stewart "Arturius" Campbell (4/6/2016)


    Interesting, never thought of doing it that way

    Nice question, thanks Horia

    Nor should you. In production code, I would always write out the explicit intent instead of relying on such a trick.

    ORDER BY CASE WHEN val IS NULL THEN 2 ELSE 1 END, -- Ensure that NULL sorts last

    val -- Within non-NULL, order by ascending value

    I'm not sure I'd call it a trick -- it's using a documented aspect of the SQL-92 standard (see my post on an early question at http://www.sqlservercentral.com/Forums/FindPost1768805.aspx

    Having said that, it's not a commonly-known aspect, so at least commenting how it works would seem in order.

    I'm not sure whether a CASE with a NULL test on every row would be more or less performant than a *-1 on every row, but that's something to consider as well.

  • david.gugg

    SSCertifiable

    Points: 5691

    Fun question, thank you.


    [font="Tahoma"]Personal blog relating fishing to database administration:[/font]

    [font="Comic Sans MS"]https://davegugg.wordpress.com[/url]/[/font]

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Nice trick. I'll keep that in mind.

  • Andy Warren

    SSC Guru

    Points: 119676

    Hugo, is there really a difference? In both cases its clearly something unusual being done and I'd argue in both cases a "why" comment would be appropriate.

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

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