SELECT FUN

Question

SELECT FUN

Viewing 5 posts - 31 through 34 (of 34 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736063 More actions · Answer 1

When a question is ambiguous and I've (usually) been chastised properly by Hugo for letting it slip through, I correct the question and then award everyone who has answered to that time, a correct mark. With points added back.

Saurabh Dwivedy SSCommitted Points: 1887 More actions · Answer 2

I got it wrong because I thought TWO NULL Values are NOT supposed to be same(identical). So I chose the option with 3 rows. Quirk of SQLServer?

Saurabh Dwivedy
___________________________________________________________

My Blog: http://tinyurl.com/dwivedys

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537

Be Happy!

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 3

saurabh.dwivedy (5/7/2009)
I got it wrong because I thought TWO NULL Values are NOT supposed to be same(identical). So I chose the option with 3 rows. Quirk of SQLServer?

No quirk. Expected behaviour, as required by the ANSI standard.

NULL values are considered to yield "unknown" (not false!) on any comparison predicate, so they are neither equal nor unequal. But that does not apply here. This is a grouping operation, and the ANSI standard explicitly states that NULL values are considered to be not distinct for grouping purposes.

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/

Saurabh Dwivedy SSCommitted Points: 1887 More actions · Answer 4

Hugo Kornelis (5/8/2009)
saurabh.dwivedy (5/7/2009)
I got it wrong because I thought TWO NULL Values are NOT supposed to be same(identical). So I chose the option with 3 rows. Quirk of SQLServer?
No quirk. Expected behaviour, as required by the ANSI standard.
NULL values are considered to yield "unknown" (not false!) on any comparison predicate, so they are neither equal nor unequal. But that does not apply here. This is a grouping operation, and the ANSI standard explicitly states that NULL values are considered to be not distinct for grouping purposes.

Thanks. Appreciate it.

Saurabh Dwivedy
___________________________________________________________

My Blog: http://tinyurl.com/dwivedys

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537

Be Happy!

Abi Chapagai SSCrazy Points: 2666 More actions · Answer 5

Hugo,

I agree with your points. I did select wrong answer but it is a good learning experience and i did not know that order by in UNION would make a difference. I had the impression that UNION would not take the nulls like UNION ALL. Thanks for the clarifying it.