Ranking

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715095

    Comments posted to this topic are about the item Ranking

  • Eirikur Eiriksson

    SSC Guru

    Points: 182344

    Thanks for the question Steve.

    Misread the 1, 2, 3, 3, 4 as 1, 2, 3, 4 and of course ticked row_number:pinch: Off for making more industrial strength espresso

    😎

  • happycat59

    One Orange Chip

    Points: 29186

    As far as I am concerned, this is another dodgy question. The sample data includes 4 rows only. Personally, I read the suggested answer and treated the extra value as a typo - you cannot get five values when you only have four rows to rank.

    Hence, I think that given the 4 rows of data, you could answer ROW_NUMBER if the expected output from "1, 2, 3, 4" or DENSE_RANK if you thought the answer should be "1, 2, 3, 3".

    My answer was ROW_NUMBER because I assumed the extra 3 in the list of results was the typo.

  • Roland C

    Hall of Fame

    Points: 3473

    Another badly redacted question ! It gets a little boring :doze:

  • Sean Pearce

    SSCoach

    Points: 15750

    Roland C (10/1/2014)


    Another badly redacted question ! It gets a little boring :doze:

    You could always submit your own question.

    The SQL Guy @ blogspot[/url]

    @SeanPearceSQL

    About Me[/url]

  • Hany Helmy

    SSChampion

    Points: 13321

    Roland C (10/1/2014)


    Another badly redacted question ! It gets a little boring :doze:

    + 1 😉

  • Eirikur Eiriksson

    SSC Guru

    Points: 182344

    happycat59 (9/30/2014)


    As far as I am concerned, this is another dodgy question. The sample data includes 4 rows only. Personally, I read the suggested answer and treated the extra value as a typo - you cannot get five values when you only have four rows to rank.

    Hence, I think that given the 4 rows of data, you could answer ROW_NUMBER if the expected output from "1, 2, 3, 4" or DENSE_RANK if you thought the answer should be "1, 2, 3, 3".

    My answer was ROW_NUMBER because I assumed the extra 3 in the list of results was the typo.

    Wrong assumption, nothing wrong with the question.

    😎

  • graeme.shorter 69931

    SSC Journeyman

    Points: 76

    I thought the "3,3" was a typo as well, I did wonder why two answers would satisfy this though (row_number, rank)

  • Toreador

    SSChampion

    Points: 11225

    Eirikur Eiriksson (10/1/2014)Wrong assumption, nothing wrong with the question.

    If you think that then I suggest you re-read the question!

  • Gail Shaw

    SSC Guru

    Points: 1004424

    Sean Pearce (10/1/2014)


    Roland C (10/1/2014)


    Another badly redacted question ! It gets a little boring :doze:

    You could always submit your own question.

    This kind of reaction is why I don't submit questions any more.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Leonidas199x

    Right there with Babe

    Points: 750

    I appreciate these questions every day, helps me learn new things, so I don't want to sound like a nob here, but surely the question is wrong?

    'If I have this data?

    CustomerID Sales

    1 100

    2 50

    3 50

    4 120'

    Working on that, to get it as 1,2,3,4 DENSE_RANK() would not work, as it would be 1,2,3,3.

    In the answer, it gives the data as:

    VALUES

    ( 1, 100), ( 2, 50), (3, 50), (4, 120), (5, 25)

    Which would relate to 1,2,3,3,4 as the question asks.

    A good one for me to look at from a learning point of view, as I had to look into the data to understand, but at first I was massively confused my answer of row_number was incorrect!

  • erwin oosterhoorn

    SSCommitted

    Points: 1995

    I agree that there should be at least one more record in the example to make sense of the suggested answer. But if you read this part:

    If I want to get the rankings of customers, and ensure that I get a rank of each number, with no skips in the results, which function do I use?

    you can use the example data and determine that the answer must be DENSE_RANK(), as that will give you 1,2,3,3,4 if there are more records, and the Row_number() will give you 1,2,3,4,5.

    Just my interpretation of the question

  • Leonidas199x

    Right there with Babe

    Points: 750

    Understood, and agree if I had picked up on that then I could have deduced that row_number would be insufficient for the requirement.

    I guess I jumped on the data example given.

  • TomThomson

    SSC Guru

    Points: 104763

    I guess it's a bit strange to supply 4 rows as an example and ask for ranking of 5 as a result, but in the question that's just an example and the requirement for rnking with no missed numbers is statedclearly so only dense_rand will work. So nothing really wrong with he question.

    It was only on reading the explanation that I realised this was a mistake and not a gentle trick - had it been a gent;e trick the number of rows in the explanation would have been 4, not 5.

    Tom

  • Hany Helmy

    SSChampion

    Points: 13321

    erwin.oosterhoorn (10/1/2014)


    I agree that there should be at least one more record in the example to make sense of the suggested answer. But if you read this part:

    If I want to get the rankings of customers, and ensure that I get a rank of each number, with no skips in the results, which function do I use?

    you can use the example data and determine that the answer must be DENSE_RANK(), as that will give you 1,2,3,3,4 if there are more records, and the Row_number() will give you 1,2,3,4,5.

    Just my interpretation of the question

    +1

    Agree

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

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