September 30, 2014 at 10:22 pm
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
September 30, 2014 at 10:37 pm
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.
October 1, 2014 at 12:18 am
Another badly redacted question ! It gets a little boring :doze:
October 1, 2014 at 12:39 am
October 1, 2014 at 12:46 am
Roland C (10/1/2014)
Another badly redacted question ! It gets a little boring :doze:
+ 1
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
October 1, 2014 at 1:22 am
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.
October 1, 2014 at 2:06 am
I thought the "3,3" was a typo as well, I did wonder why two answers would satisfy this though (row_number, rank)
October 1, 2014 at 2:19 am
Eirikur Eiriksson (10/1/2014)Wrong assumption, nothing wrong with the question.
If you think that then I suggest you re-read the question!
October 1, 2014 at 2:29 am
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
October 1, 2014 at 3:20 am
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!
October 1, 2014 at 3:28 am
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
October 1, 2014 at 3:31 am
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.
October 1, 2014 at 4:29 am
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
October 1, 2014 at 5:05 am
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
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
Viewing 15 posts - 1 through 15 (of 47 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy