Christmas 2017 - Part 3

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 721524

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

  • Evgeny Garaev

    SSCertifiable

    Points: 6829

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

  • HappyGeek

    SSCoach

    Points: 18684

    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

    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: 148777

    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: 7649

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

  • gvoshol 73146

    Hall of Fame

    Points: 3199

    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

    Old Hand

    Points: 312

    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

  • gvoshol 73146

    Hall of Fame

    Points: 3199

    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

    Old Hand

    Points: 312

    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: 2699

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

  • TomThomson

    SSC Guru

    Points: 104773

    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: 721524

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

  • TomThomson

    SSC Guru

    Points: 104773

    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: 9277

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

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

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