Ranking

Question

Ranking

Steve Jones - SSC Editor

SSC Guru

Points: 736183
More actions
September 30, 2014 at 8:56 pm

#311905

Comments posted to this topic are about the item Ranking

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

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

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 1

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: 29389 More actions · Answer 2

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 More actions · Answer 3

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

Sean Pearce SSCoach Points: 15999 More actions · Answer 4

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: 13921 More actions · Answer 5

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

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 6

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 More actions · Answer 7

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: 11365 More actions · Answer 8

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: 1004504 More actions · Answer 9

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

no longer ssc user Right there with Babe Points: 780 More actions · Answer 10

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 SSCrazy Points: 2316 More actions · Answer 11

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

no longer ssc user Right there with Babe Points: 780 More actions · Answer 12

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: 104773 More actions · Answer 13

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: 13921 More actions · Answer 14

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