Christmas 2017 - Part 3

Question

Post reply

Christmas 2017 - Part 3

Steve Jones - SSC Editor

SSC Guru

Points: 734418
More actions
December 26, 2017 at 8:06 pm

#77338

Comments posted to this topic are about the item Christmas 2017 - Part 3

Viewing 15 posts - 1 through 15 (of 16 total)

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

Evgeny Garaev SSCertifiable Points: 7422 More actions · Answer 1

ORDER BY Movie will give the same result...
Also you have a mistake in your query.

HappyGeek SSCoach Points: 18726 More actions · Answer 2

Continuing the theme, but this time with the added bonus of not only a syntax error but also the number of columns appear different! Must be all that Christmas Cheer!! Thanks for the question Steve.

...

sTTu SSC Veteran Points: 201 More actions · Answer 3

Cut and paste this query (without the order by) and it doesn't run. Also "A Christmas Carol" comes before "A Wonderful Life" so ordering by Movie is correct.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

Evgeny - Tuesday, December 26, 2017 8:07 PM
ORDER BY Movie will give the same result...
Also you have a mistake in your query.

Yes, I don't know why the movie is called 's a Wonderful Life here instead of It's a Wonderful Life - I suspect that's a typo. Be that as it may, it appears to depend on collation. Compare the two result sets here:
SELECT * FROM (VALUES ('A Christmas Carol'),('''s a Wonderful Life')) AS a (John) ORDER BY John COLLATE SQL_Latin1_General_CP1_CI_AS

SELECT * FROM (VALUES ('A Christmas Carol'),('''s a Wonderful Life')) AS a (John) ORDER BY John COLLATE Latin1_General_CI_AS

I don't know whether this happens because A appears before ' in one collation but after in the other, or because one collation ignores leading apostrophes but the other does not. I too guessed Movie, and was disappointed that it turned out to be the "wrong" answer.

John

PurpleLady SSCertifiable Points: 7815 More actions · Answer 5

Both locale desc and movie work in this query - after correcting the movie name that is.

GaryV Hall of Fame Points: 3307 More actions · Answer 6

PurpleLady - Wednesday, December 27, 2017 4:31 AM
Both locale desc and movie work in this query - after correcting the movie name that is.

And after correcting the data in general to give Wonderful Life a locale.
I suspect the missing "It" was intentional, maybe to make the point about collation sequence that John identified above.

trowley SSC-Addicted Points: 499 More actions · Answer 7

The correct answer is Movie
I had to add a Genre to the last record set and correct the syntax for the movie title [It's a Wonderful Life] to get this to run.
Only order by Movie will give you Ebenezer Scrooge
if you order by Locale the answer is Frosty,
if you order by Age the answer is George Bailey,
if you order by Genre the answer is Frosty,
if you order by SomeName the answer is Dumbledore.

SELECT TOP 1

answer = SomeName

FROM

( VALUES ('Ebenezer Scrooge', 'A Christmas Carol', 'Old', 'Fiction', 'London'),

('Dumbledore', 'Harry Potter and the Sorcerer''s Stone', 'Very Old', 'Fantasy', 'Hogwarts'),

('Frosty', 'Frosty The Snowman', 'Infant', 'Animated', 'A small town'),

('George Bailey','It''s a Wonderful Life', 'Adult', 'Drama', 'Falls')

) AS a (SomeName, Movie, Age, Genre, Locale)

ORDER BY Movie

GaryV Hall of Fame Points: 3307 More actions · Answer 8

^

But if you sort by locale DESCENDING as in the possible answers, you will get the correct answer if you supply all the columns for George Bailey's record. That happens whether you complete the movie title or not.

trowley SSC-Addicted Points: 499 More actions · Answer 9

gvoshol 73146 - Wednesday, December 27, 2017 8:02 AM
^
But if you sort by locale DESCENDING as in the possible answers, you will get the correct answer if you supply all the columns for George Bailey's record. That happens whether you complete the movie title or not.

You are quite correct.

Bobby Russell SSCrazy Points: 2801 More actions · Answer 10

Order By Movie will also provide the correct answer, yet it tells me I'm incorrect?....:Whistling:

TomThomson SSC Guru Points: 104773 More actions · Answer 11

I don't like questions that have broken SQL, nor do I like questions where we
have to guess which of two correct answers will be labelled "wrong" instead of "correct".

It's irritating.

Tom

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 12

Sorry, mistake in the cut and paste. Code corrected and points awarded back.

TomThomson SSC Guru Points: 104773 More actions · Answer 13

Steve Jones - SSC Editor - Wednesday, December 27, 2017 4:35 PM
Sorry, mistake in the cut and paste. Code corrected and points awarded back.

This clearly hasn't happened. The answer "Movie" is still perfectly correct. you don't appear to have awarded points back as the your stats still show the same old numbers for each option, and still insist that "Move" is incorrect.

Did you really think that with four movie titles beginning with "A", "H", "F" and "I" something other than the title beginning with "A" will determine which row is selected
"by top 1 ... order by "movie"?

Of course changing the "movie" option to "movie desc" means that it's no longer a correct answer. With that change the stats indicate that a majority of answers were incompetent nonsense, which is not good for the reputation of the people who use this website, and of course now you can pretend that most people did get it wrong.

Tom

George Vobr SSCrazy Eights Points: 9914 More actions · Answer 14

I'm sorry, but the additional fix of the response options is not fair. 😉
Happy New Year!