# Ranking

• Steve Jones - SSC Editor

SSC Guru

Points: 717408

• Eirikur Eiriksson

SSC Guru

Points: 182410

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

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

• Hany Helmy

SSChampion

Points: 13436

Roland C (10/1/2014)

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

+ 1 😉

• Eirikur Eiriksson

SSC Guru

Points: 182410

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)

SSChampion

Points: 11248

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

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

SSCrazy

Points: 2100

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

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

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)