ORDER BY ASC and NULLs last

  • Never thought of doing it this way. Thanks!

  • Andy Warren (4/7/2016)


    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.

    I personally like to be explicit when I create an ORDER BY that treats a selection of the data different. The CASE in the ORDER BY makes that very clear.

    It is a bit more typing, of course, but I would otherwise add a comment to the ORDER BY to clarify why I use the prefix minus operator and the descending order to get an ascending order with a twist.

    It os of course very much a matter of personal preference. 😉


    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/

  • Andy Warren (4/7/2016)


    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.

    Yes, there is really a difference.

    sknox (4/7/2016)


    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.

    No, Hugo is right, it has to be considered a trick rather than a valid technique.

    If the column had been declared with a numeric type other than int or bigint or smallint it would have been a reliable technique, not a trick, but as the declaration given uses int, here we have an unreliable trick.

    It will usually work, but what happens if one of the integer values is -2147483648 ? Applying minus to that will cause arithmetic overflow, so it's unsafe to use with ints. It's equally unsafe with bigints (try it with the value -9223372036854775808). It works for smallints because (contrary to BOL, so presumably this is a bug in SQL Server) unary minus on a smallint promotes the result to int. It works for tinyint (unary minus is documented as promoting tinyint to smallint; unary minus obviously can't deliver tinyint because because there are no negative tinyints). It works for all the non-integer numerics (because their negative values are handled with a separate sign bit, not using twos complement so that the sign bit can be part of the absolute magnitude).

    Tom

  • 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

    +1

    Production code shouldn't make you scratch your head to figure out how it's supposed to function. It's hard enough trying to figure out why code doesn't work as intended, without the added challenge of trying to determine the coder's original goal.

    Nice QotD, but I wouldn't implement this in production code either. CASE statements ("expressions"? I can never remember which is semantically correct) are largely self-documenting, and the addition of a quick comment nails down the intent firmly.

    Rich

  • 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

    Hugo, thanks for sharing this - it's elegant and works regardless of what your other sort operators are.

  • Nice and interesting question for which I have chosen the bad proposal. I need to study this topic. I will do it during this afternoon. Anyway , an useful question so thanks Horia...

  • Great question, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • 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.

    Agreed. Tricks are cute and fun but should be used sparingly. The next programmer that looks at it may not understand your trick. I consistently use the case statement as you described.

  • It will also work if you write your query like below

    SELECT val FROM @table ORDER BY val desc

  • gauravsrmsin (8/2/2016)


    It will also work if you write your query like below

    SELECT val FROM @table ORDER BY val desc

    How exactly would that sort in ascending order?


    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/

  • It will work as per required result and give the following result.

    1

    0

    -1

    NULL

  • gauravsrmsin (8/3/2016)


    It will work as per required result and give the following result.

    1

    0

    -1

    NULL

    The problem posed in the question is this (emphasis mine):

    "I need to return the data sorted ascending and with NULLs at the end of the results."

    Your result set is descending and with NULLs at the end of the results.


    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/

  • Sorry...

    that's mean.. i didn't attention on the question

Viewing 13 posts - 16 through 28 (of 28 total)

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