Post reply

ORDER BY and NULL

  • March 10, 2016 at 8:33 pm

    #311982

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

  • March 10, 2016 at 10:54 pm

    This was removed by the editor as SPAM

  • March 11, 2016 at 12:15 am

    #1864491

    Easy one for end of week, thanks

    ...

  • March 11, 2016 at 12:39 am

    #1864495

    Easy one, thanks.

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

  • March 11, 2016 at 2:09 am

    #1864505

    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

  • March 11, 2016 at 6:02 am

    #1864541

    Nice straightforward question.

    Tom

  • March 11, 2016 at 6:16 am

    #1864553

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

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • March 11, 2016 at 7:13 am

    #1864573

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

  • March 11, 2016 at 7:41 am

    #1864579

    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.

  • March 11, 2016 at 9:04 pm

    #1864710

    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.

  • March 12, 2016 at 5:19 pm

    #1864766

    Nice & easy question, thanks.

  • March 12, 2016 at 11:41 pm

    #1864779

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

    Thanks & Best Regards,
    Hany Helmy
    SQL Server Database Consultant

  • March 14, 2016 at 7:18 am

    #1864907

    Thanks for the question.

  • March 14, 2016 at 9:06 am

    #1864960

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

    🙂

    Rich

  • March 14, 2016 at 9:20 am

    #1864970

    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