SELECT FUN

Question

SELECT FUN

Viewing 15 posts - 16 through 30 (of 34 total)

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

Michael Poppers SSCrazy Points: 2151 More actions · Answer 1

r.hensbergen (4/22/2009)
Jayeff (4/22/2009)
mike (4/22/2009)
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?
Yeah, me too!
And see, you both got you point just by posting that you want your point back. 😉

[font="Verdana"]Good point :)...but shouldn't these Qs be screened for accuracy?[/font]

john.arnott SSChampion Points: 11882 More actions · Answer 2

SanjayAttray (4/22/2009)
select NULL,'manoj'union select Null,'1'union select NULL,'manoj'
order by 2
or
select NULL,'manoj'union select Null,'1'union select NULL,'manoj'
order by 1
gives you same result.
NULL1
NULLmanoj
I 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.

Try actually specifying an "order by" that can be honored by the dbms:select NULL,'manoj'

union select Null,'1'

union select NULL,'manoj'

order by 2 desc

This returns the same two rows in a different order:

----------- -----

NULL manoj

NULL 1

(2 row(s) affected)

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.

SanjayAttray SSChampion Points: 13157 More actions · Answer 3

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'

order by 2 desc

SQL DBA.

Chris Howarth-536003 SSCommitted Points: 1775 More actions · Answer 4

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,manoj

NULL,1

...or:

NULL,1

NULL,manoj

Note that all of the answers in the original question have single quotes around manoj.

Chris

brewmanz SSCommitted Points: 1575 More actions · Answer 5

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

NULL, '1' and NULL,'manoj' (2 rows)

rather than

NULL, 1 and NULL,'manoj' (2 rows)

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.

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

Steve Jones - SSC Editor

SSC Guru

Points: 736063

April 23, 2009 at 9:55 am

#983054

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

DPhillips-731960 Hall of Fame Points: 3904 More actions · Answer 7

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

Arsi Salo Ten Centuries Points: 1313 More actions · Answer 8

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

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

Arsi Salo (4/24/2009)
... And now the original correct choice does not give point...:ermm:

... which is entirely correct, since the question has been changed to (emphasis mine):

"What can be the output for this code? (select all 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 can be the output (even if only one of them actually is when you run the code).

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/

Jamie-2229 SSCrazy Eights Points: 8156 More actions · Answer 10

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

Jamie

Mohamed-401375 SSC Eights! Points: 965 More actions · Answer 11

Interesting question. Highlights the features of two clauses- UNION & ORDER BY

but 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.

Reginald J Ray Jr Old Hand Points: 383 More actions · Answer 12

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 :^)...

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

I know the test developer couldn't have made a mistake

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 (now!) specifically asks what can 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.

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/

Jamie-2229 SSCrazy Eights Points: 8156 More actions · Answer 14

select all that apply

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?

Jamie

Reginald J Ray Jr Old Hand Points: 383 More actions · Answer 15

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