SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by mverma4you / SELECT FUN / Latest Posts

RE: SELECT FUN

Abi Chapagai — Fri, 24 Dec 2010 08:47:05 GMT

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.

RE: SELECT FUN

Saurabh Dwivedy — Fri, 08 May 2009 02:20:01 GMT

[quote][b]Hugo Kornelis (5/8/2009)[/b][hr][quote][b]saurabh.dwivedy (5/7/2009)[/b][hr]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?[/quote]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.[/quote]Thanks. Appreciate it.

RE: SELECT FUN

Hugo Kornelis — Fri, 08 May 2009 02:12:42 GMT

[quote][b]saurabh.dwivedy (5/7/2009)[/b][hr]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?[/quote]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.

RE: SELECT FUN

Saurabh Dwivedy — Thu, 07 May 2009 21:10:33 GMT

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?

RE: SELECT FUN

Steve Jones - SSC Editor — Tue, 05 May 2009 09:22:30 GMT

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.

RE: SELECT FUN

Reginald J Ray Jr — Tue, 05 May 2009 08:48:14 GMT

That's a nice thought but not necessary - I just got 2 points credit on a different question - using totally false reasoning - so we're more than even !!Thanks

RE: SELECT FUN

Jamie Longstreet-481950 — Tue, 05 May 2009 08:32:58 GMT

[quote]select all that apply[/quote]Steve frequently goes in and gives credit even if the user does not "select all that apply'. Take note of a qot from 4/27/09. Question says all that apply but credit was given anyway. Perhaps it should be the case here as well?

RE: SELECT FUN

Hugo Kornelis — Tue, 05 May 2009 08:19:01 GMT

[quote]I know the test developer couldn't have made a mistake[/quote]Hi Reginald,Do I sense some sarcasm there? ;-)Anyway, if you selected both #1 and #2, then you should have gotten the point. If you chose one of them at random, you shouldn't - since the question ([b]now![/b]) specifically asks what [i]can[/i] be the output, and asks you to "select all that apply".Read the preceeding discussion for more information about the original phrasing of the question and why this was changed.

RE: SELECT FUN

Reginald J Ray Jr — Tue, 05 May 2009 08:12:29 GMT

I chose #1, or maybe #2, but either one is correct, and it told me I was wrong. I know the test developer couldn't have made a mistake so I guess the computer just messed up :^)...

RE: SELECT FUN

Mohamed-401375 — Mon, 27 Apr 2009 12:43:25 GMT

Interesting question. Highlights the features of two clauses- UNION & ORDER BYbut the phrasing of the question was kind of misleading. That has caused lots of folks to skip the second answer. I agree with Hugo Kornelis that the question could have been phrased a little more explicitly.

RE: SELECT FUN

Jamie Longstreet-481950 — Mon, 27 Apr 2009 05:57:40 GMT

When it comes to fun and humor (on SQLSERVERCENTRAL), I am very good at getting the answer wrong.

RE: SELECT FUN

Hugo Kornelis — Fri, 24 Apr 2009 02:21:01 GMT

[quote][b]Arsi Salo (4/24/2009)[/b][hr]... And now the original correct choice does not give point...:ermm:[/quote]... which is entirely correct, since the question has been changed to (emphasis mine):"What [b]can be[/b] the output for this code? (select [b]all[/b] that apply)"And as already discussed at length in this topic, the query only defines which rows to be returned and not the order, so both versions [i]can[/i] be the output (even if only one of them actually is when you run the code).

RE: SELECT FUN

Arsi Salo — Fri, 24 Apr 2009 02:13:35 GMT

... And now the original correct choice does not give point...:ermm:

RE: SELECT FUN

DPhillips-731960 — Thu, 23 Apr 2009 11:13:22 GMT

Too Funny. I clicked the wrong box... but interesting point just the same. Thanks for the questions!

RE: SELECT FUN

Steve Jones - SSC Editor — Thu, 23 Apr 2009 09:55:17 GMT

Points awarded back and both 1 and 2 marked as correct.

RE: SELECT FUN

brewmanz — Thu, 23 Apr 2009 04:08:11 GMT

I got it right (guessed that it would sort alphabetically to remove duplicates, but that's not guaranteed, as mentioned)However, I reckon that *none* of the answers are (or should that be 'is?) correct.I would prefer to see[b]NULL, '1' and NULL,'manoj' (2 rows)[/b] rather than [b]NULL, 1 and NULL,'manoj' (2 rows)[/b] as the answer, as all the second-field values are clearly stated as character strings.Some may call that being picky, but hey, two of the 'mutually exclusive' answers are actually the same, according to set theory.

RE: SELECT FUN

Chris Howarth-536003 — Wed, 22 Apr 2009 12:50:50 GMT

Maybe I'm being pedantic, but I don't think that any of the answers provided are correct.If you interpret a comma to be a column delimiter, the results should be either:NULL,manojNULL,1...or:NULL,1NULL,manojNote that [b]all[/b] of the answers in the original question have single quotes around manoj.Chris

RE: SELECT FUN

SanjayAttray — Wed, 22 Apr 2009 12:19:53 GMT

John,With " order by 2 desc " are you not forcing the query to get result in the way you had desired?select NULL,'manoj'union select Null,'1'union select NULL,'manoj'[b]order by 2 desc[/b]

RE: SELECT FUN

john.arnott — Wed, 22 Apr 2009 12:04:57 GMT

[quote][b]SanjayAttray (4/22/2009)[/b][hr]select NULL,'manoj'union select Null,'1'union select NULL,'manoj'order by 2orselect NULL,'manoj'union select Null,'1'union select NULL,'manoj'order by 1gives you same result.NULL 1NULL manojI tried on all sql server versions and result is same. May be I am missing something here when some people are telling both 1 & 2 answers are correct.[/quote]Try actually specifying an "order by" that can be honored by the dbms:[code]select NULL,'manoj'union select Null,'1'union select NULL,'manoj'order by 2 desc[/code]This returns the same two rows in a different order: [code] ----------- -----NULL manojNULL 1(2 row(s) affected)[/code]So, the difference between the two 2-rows-returned answers comes down to whether you assume the same collation was chosen (or accepted) at installation time. I lost the point because as a mainframe programmer I've got EBCDIC stuck in my head and figured that letters would sort before numbers. But even as I thought that, I was grumbling to myself that without an "order by", it's a crap-shoot between the two.

RE: SELECT FUN

Michael Poppers — Wed, 22 Apr 2009 11:14:03 GMT

[quote][b]r.hensbergen (4/22/2009)[/b][hr][quote][b]Jayeff (4/22/2009)[/b][hr][quote][b]mike (4/22/2009)[/b][hr]Hey! Not fair. I lost a point because of a missing ORDER BY ? Like a previous poster mentioned, both 1 and 2 are correct. :crying:Can I get my point back please? [/quote]Yeah, me too![/quote]And see, you both got you point just by posting that you want your point back. ;)[/quote][font="Verdana"][size="4"]Good point :)...but shouldn't these Qs be screened for accuracy?[/size][/font]

RE: SELECT FUN

Derek Dongray — Wed, 22 Apr 2009 10:43:04 GMT

RE: SELECT FUN

Ronald H — Wed, 22 Apr 2009 09:52:48 GMT

[quote][b]Jayeff (4/22/2009)[/b][hr][quote][b]mike (4/22/2009)[/b][hr]Hey! Not fair. I lost a point because of a missing ORDER BY ? Like a previous poster mentioned, both 1 and 2 are correct. :crying:Can I get my point back please? [/quote]Yeah, me too![/quote]And see, you both got you point just by posting that you want your point back. ;)

RE: SELECT FUN

SanjayAttray — Wed, 22 Apr 2009 07:49:27 GMT

select NULL,'manoj'union select Null,'1'union select NULL,'manoj'order by 2orselect NULL,'manoj'union select Null,'1'union select NULL,'manoj'order by 1gives you same result.NULL 1NULL manojI tried on all sql server versions and result is same. May be I am missing something here when some people are telling both 1 & 2 answers are correct.

RE: SELECT FUN

beezell — Wed, 22 Apr 2009 06:26:39 GMT

From Microsoft: "The exact results of a UNION operation depend on the collation chosen during installation and the ORDER BY clause."http://msdn.microsoft.com/en-us/library/ms191141(SQL.90).aspxThis is for SQL Server 2005. I'm assuming it applies to other versions as well. It appears that behind the scenes SQL Server does sort it as part of the duplicate elimination process. This will not be done with UNION ALL though.For the record: An ORDER BY should be used as part of best practices.Cheers,Brian

RE: SELECT FUN

Jayeff — Wed, 22 Apr 2009 06:26:22 GMT

[quote][b]mike (4/22/2009)[/b][hr]Hey! Not fair. I lost a point because of a missing ORDER BY ? Like a previous poster mentioned, both 1 and 2 are correct. :crying:Can I get my point back please? [/quote]Yeah, me too!

RE: SELECT FUN

mike-658424 — Wed, 22 Apr 2009 05:43:07 GMT

Hey! Not fair. I lost a point because of a missing ORDER BY ? Like a previous poster mentioned, both 1 and 2 are correct. :crying:Can I get my point back please?

RE: SELECT FUN

dave.farmer — Wed, 22 Apr 2009 04:54:47 GMT

I feel cheated - I chose the second so got it wrong, but that seems harsh to me as the data is unordered, isn't it?

RE: SELECT FUN

Atif-ullah Sheikh — Wed, 22 Apr 2009 03:21:16 GMT

On seeing the question, i thought there might be a Default Order of the result set returned by the query without the order by clause. But after a research, I came to know (and I was sure about the results) that there is no guarantee of ordering of data returned by the query without the Order by clause along with the fact that the queried table DO NOT have any Index on it. Half heartedly selected the First option and got the point...:-D

RE: SELECT FUN

EdVassie — Wed, 22 Apr 2009 03:04:42 GMT

I think previous posters have said all that is needed. Either option 1 or option 2 are correct as there is no ORDER BY clause. It would be interesting to run this query a few thousand times on a very busy server with at least 4 cores, to see if thre is a 50-50 split between the two possible results.

RE: SELECT FUN

Rachel Byford — Wed, 22 Apr 2009 02:19:15 GMT

Very interesting, thank you.I agree absolutely with your statement that if you want something order it, then you should always specify an order by clause.

RE: SELECT FUN

Hugo Kornelis — Wed, 22 Apr 2009 02:13:44 GMT

[quote][b]Rachel Byford (4/22/2009)[/b][hr]My belief was that as a consequence of the union removing duplicates, the output always came out ordered. Possibly not if you end up with several worker processes doing the work, but in this case that won't happen.[/quote]Hi Rachel,Several workers processing the output is extremely unlikely, given the small amount of data. But the method of removing duplicates can affect the results. SQL Server has different strategies for removing duplicates. The one you are thinking of uses a Sort step to bring duplicates together, followed by a Stream Aggregate step to remove them. This one is not used by this query (at least not on my machine; as I already indicated, this depends on a lot of factors and results may vary based on different versions of SQL Server, different hardware, and different workload).The technique ussed on my computer (I checked by running the query with the "include actual execution plan" option activated) is to use a merge join between three one-row "tables". Due to how merge works, this will also cause the result set to be sorted.A third technique for removing duplicates is a hash union. You can force SQL Server to use this technique by adding "OPTION (HASH UNION)" at the end of the query. On my machine, this changed the order of the output rows (though it might not have this effect on all machines, as the order now depends on the actual hashing function used, which is as far as I know not documented and might change between versions or even between service packs).These are the three techniques for union that I am aware of. There may be more. And if there are no more now, they might be introduced at a later time, if someone in the SQL Server optimizer team comes up with a bright idea.I can only repeat what I already said a million times: order of rows is never guaranteed, unless an explicit ORDER BY is present on the outermost query!

RE: SELECT FUN

Rachel Byford — Wed, 22 Apr 2009 01:49:39 GMT

My belief was that as a consequence of the union removing duplicates, the output always came out ordered. Possibly not if you end up with several worker processes doing the work, but in this case that won't happen.

RE: SELECT FUN

Hugo Kornelis — Wed, 22 Apr 2009 01:23:29 GMT

[quote][b]Carlo Romagnano (4/22/2009)[/b][hr]Also the second answer is correct, cause of lack of ORDER BY.[/quote]Agreed. Since there is no ORDER BY, both answer options #1 and #2 are correct. I chose the first, at random, and got my point. I then ran the code to see that, on my computer, my version of SQL Server, the current service pack and hotfix level, and the current workload on the machine, this was indeed the result - but there is no guarantee at all that the same ordering of rows willl be reproduced the next time I run this code.Not a bad question for understanding that UNION removes duplicates, and that two NULLs, even though they don't compare as equal, are considered duplicate - but a very bad question for including two versions of the same answer, in a different order, and only showing one of tem as correct.@Steve: Probabaly the best way to fix this is to change the question to read: "what [i]can be[/i] the output of this code (check all that apply)", and mark both answers #1 and #2 as correct.

RE: SELECT FUN

Carlo Romagnano — Wed, 22 Apr 2009 00:52:57 GMT

this question is the same of "predict output".Also the second answer is correct, cause of lack of ORDER BY.

SELECT FUN

mverma4you — Tue, 21 Apr 2009 21:24:18 GMT

Comments posted to this topic are about the item [B]SELECT FUN[/B]