ORDER BY and NULL

  • Comments posted to this topic are about the item ORDER BY and NULL

  • This was removed by the editor as SPAM

  • Easy one for end of week, thanks

    ...

  • Easy one, thanks.

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

  • Interesting. I had assumed that NULL is always listed first in an ORDER BY. Indeed it is, if you order ascending. But if you add DESC to the end of the code, NULL comes last, so BOL is correct in stating that NULL is treated as the lowest value. Thanks for the question!

    John

  • Nice straightforward question.

    Tom

  • In my opinion, the sort results should be unpredictable because NULL is an unknown/unspecified value, which implies it cannot be sorted.

    I'm not arguing that the answer is incorrect. Practically, I like that there is a predictable behavior, because it makes our jobs a lot easier. Philosophically, I just don't agree with the way SQL Server handles this. Is this in line with the ANSI standard?

    Anyone want to join me to discuss the philosophy of NULL over a pitcher of beer? (:

  • Kaye Cahs (3/11/2016)


    In my opinion, the sort results should be unpredictable because NULL is an unknown/unspecified value, which implies it cannot be sorted.

    I'm not arguing that the answer is incorrect. Practically, I like that there is a predictable behavior, because it makes our jobs a lot easier. Philosophically, I just don't agree with the way SQL Server handles this. Is this in line with the ANSI standard?

    Anyone want to join me to discuss the philosophy of NULL over a pitcher of beer? (:

    You said well: "In your opinion". In the real word, Null values are treated as the lowest possible values.

  • Kaye Cahs (3/11/2016)


    In my opinion, the sort results should be unpredictable because NULL is an unknown/unspecified value, which implies it cannot be sorted.

    I'm not arguing that the answer is incorrect. Practically, I like that there is a predictable behavior, because it makes our jobs a lot easier. Philosophically, I just don't agree with the way SQL Server handles this. Is this in line with the ANSI standard?

    Anyone want to join me to discuss the philosophy of NULL over a pitcher of beer? (:

    From SQL-92 (http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt) page 531 (Section 20.2 <direct select statement: multiple rows> General Rules):

    5) If an <order by clause> is specified, then the ordering of rows

    of the result is effectively determined by the <order by clause>

    as follows:

    a) Each <sort specification> specifies the sort direction for

    the corresponding sort key Ki. ...

    b) Let X and Y be distinct rows in the result table, and let

    XVi and YVi be the values of Ki in these rows, respectively.

    ...Whether a sort key value that

    is null is considered greater or less than a non-null value

    is implementation-defined, but all sort key values that are

    null shall either be considered greater than all non-null

    values or be considered less than all non-null values (emphasis mine)...

    So yes, it's in line with the standard. And it makes logical sense too:

    1. All of the other values are known, so not sorting them would be against the wishes of the user for no good reason.

    2. All of the nulls are known to be unspecified, so placing them within the list of specified values would be including them within a group to which they don't belong.

    3. Placing them before or after the known values is arbitrary, but it should be done consistently; hence the standard allows implementations to choose one, so their placement is predictable per implementation.

  • Nice & easy question, thanks.

  • Never had the opportunity to test this before, NULL < -1, interesting, thanx for the question.

    Thanks & Best Regards,
    Hany Helmy
    SQL Server Database Consultant

  • Thanks for the question.

  • Scratched my head a bit at that first ORDER BY (on the INSERT). Was that just smoke to trip us up?

    🙂

    Rich

  • Rich Mechaber (3/14/2016)


    Scratched my head a bit at that first ORDER BY (on the INSERT). Was that just smoke to trip us up?

    🙂

    Rich

    No, the ORDER BY in the SELECT of the INSERT is just for one of the incorrect answer: "Same order as inserted"

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

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