ORDER BY and NULL

  • Carlo Romagnano

    SSC-Insane

    Points: 21872

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

  • This was removed by the editor as SPAM

  • HappyGeek

    SSCoach

    Points: 18676

    Easy one for end of week, thanks

    ...

  • Koen Verbeeck

    SSC Guru

    Points: 258955

    Easy one, thanks.

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

  • John Mitchell-245523

    SSC Guru

    Points: 148626

    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

  • TomThomson

    SSC Guru

    Points: 104772

    Nice straightforward question.

    Tom

  • Ed Wagner

    SSC Guru

    Points: 286969

    Good question. Only one answer had me pause and wonder.

  • Kaye Cahs

    SSCarpal Tunnel

    Points: 4135

    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? (:

  • Carlo Romagnano

    SSC-Insane

    Points: 21872

    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.

  • sknox

    SSChampion

    Points: 12284

    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.

  • mjagadeeswari

    SSC-Addicted

    Points: 454

    Nice & easy question, thanks.

  • Hany Helmy

    SSChampion

    Points: 13436

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

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the question.

  • Rich Mechaber

    SSChampion

    Points: 10935

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

    🙂

    Rich

  • Carlo Romagnano

    SSC-Insane

    Points: 21872

    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 16 total)

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