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