RANK - 1

Question

RANK - 1

Ron McCullough

SSC Guru

Points: 63877
More actions
January 5, 2013 at 9:17 pm

#268464

Comments posted to this topic are about the item RANK - 1
If everything seems to be going well, you have obviously overlooked something.
Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]
January 7, 2013 at 1:32 am

This was removed by the editor as SPAM

Viewing 14 posts - 1 through 13 (of 13 total)

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

Easy one for this Monday morning, thanks! 🙂

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Lokesh Vij SSChampion Points: 10836 More actions · Answer 2

Ah! Easy one to start the week. Thanks Ron 🙂

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

Dineshbabu Hall of Fame Points: 3220 More actions · Answer 3

I don't have adventureworks DB, I answered by keeping the basic diff b/w RANK and DENSE_RANK and got it correct.

Thanks for remebering the basics...

--
Dineshbabu
Desire to learn new things..

call.copse SSCoach Points: 18050 More actions · Answer 4

Nice question, I got the answer wrong although I worked it out correctly,:blush: by foolishly selecting the wrong answer before thinking what I meant.

Dana Medley SSCertifiable Points: 6764 More actions · Answer 5

Great question, unfortunately I got it wrong. I got click happy this morning.

Everything is awesome!

(Bob Brown) SSCrazy Points: 2705 More actions · Answer 6

Thanks for the question. Wasn't real familiar with these functions and I paid for it.

TomThomson SSC Guru Points: 104773 More actions · Answer 7

This would have been a good question and easy to get right if the schemaname had been spelt "Sales" instead of "SALES". However, I happened to remember that the database database collation for AdventureWorks2008R2 is Case Sensitive. Or at least that's what it is when installed out of the box on my laptop. My server default collation is NOT case sensitive, so it wasn't picking up case sensitivity from my server default, it's in AdventureWorks as supplied by MS. So I answered "neither" instead of "rank", because both queries will fail with an invalid oject name error.

Apparently the spelling was a mistake, since that was not the "correct" answer. Not at all an error I would expect to see in one of your questions, Ron! I can't see how the queries can have been tested.

Tom

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 8

L' Eomot Inversé (1/7/2013)
This would have been a good question and easy to get right if the schemaname had been spelt "Sales" instead of "SALES". However, I happened to remember that the database database collation for AdventureWorks2008R2 is Case Sensitive. Or at least that's what it is when installed out of the box on my laptop. My server default collation is NOT case sensitive, so it wasn't picking up case sensitivity from my server default, it's in AdventureWorks as supplied by MS. So I answered "neither" instead of "rank", because both queries will fail with an invalid oject name error.
Apparently the spelling was a mistake, since that was not the "correct" answer. Not at all an error I would expect to see in one of your questions, Ron! I can't see how the queries can have been tested.

Tom, AdventureWorks is available in different flavors. Apparently, you downloaded and installed the case sensitive version, but there is a case insensitive version as well.

I think the question could have been shortened to simply present two queries, one with RANK() and one with DENSE_RANK(), and then ask which of the two could have produced the given output. That would have made it more clear that the reader should focus on the difference between those two functions.

That being said - I do like the question! Thanks, Ron!

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

TomThomson SSC Guru Points: 104773 More actions · Answer 9

Hugo Kornelis (1/7/2013)
Tom, AdventureWorks is available in different flavors. Apparently, you downloaded and installed the case sensitive version, but there is a case insensitive version as well.
I think the question could have been shortened to simply present two queries, one with RANK() and one with DENSE_RANK(), and then ask which of the two could have produced the given output. That would have made it more clear that the reader should focus on the difference between those two functions.
That being said - I do like the question! Thanks, Ron!

Writing the question with the schema name in the same case as the schema name in the database would have been a smaller change that at least ensured that it worked for both flavours of adventureworks (assuming of course that it is the same in both flavours). I guess Ron wasn't aware of the case sensitive version, as I'm sure he would either have made sure his question worked for both versions or specified which version it worked for, because it's clear that he's usually meticulous about that sort if detail in his questions. If I'd been aware that it came in several versions, I would have made sure to have a case-insensitive one, as I dislike case-sensitive collations because they are a pain when one wants dictionary order.

I like the question too, apart from the one issue of the case of the schema name. It is a nice basic question about the two ranking functions.

Tom

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

Yippee skippee - thanks Ron.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Andre Ranieri SSCrazy Points: 2819 More actions · Answer 11

Andre Ranieri

SSCrazy

Points: 2819

January 7, 2013 at 10:34 pm

#1574612

Thanks for the great question!

Andre Ranieri

asifkareem Ten Centuries Points: 1089 More actions · Answer 12

asifkareem

Ten Centuries

Points: 1089

January 15, 2013 at 1:14 pm

#1577499

Good And Easy One