March 10, 2016 at 8:33 pm
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
Easy one for end of week, thanks
...
March 11, 2016 at 12:39 am
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
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
Nice straightforward question.
Tom
March 11, 2016 at 6:16 am
Good question. Only one answer had me pause and wonder.
March 11, 2016 at 7:13 am
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
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
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
Nice & easy question, thanks.
March 12, 2016 at 11:41 pm
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
Thanks for the question.
March 14, 2016 at 9:06 am
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
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