SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Question of the Day for 26 Apr 2006


Question of the Day for 26 Apr 2006

Author
Message
Site Owners
Site Owners
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: Administrators
Points: 12701 Visits: 22
Comments posted to this topic are about the Question of the Day for 26 Apr 2006 posted at http://www.sqlservercentral.com/testcenter/qod.asp?QuestionID=758.
lakusha
lakusha
SSC Eights!
SSC Eights! (873 reputation)SSC Eights! (873 reputation)SSC Eights! (873 reputation)SSC Eights! (873 reputation)SSC Eights! (873 reputation)SSC Eights! (873 reputation)SSC Eights! (873 reputation)SSC Eights! (873 reputation)

Group: General Forum Members
Points: 873 Visits: 7

I got it right by guessing, since the choice of words was misleading. The OVER clause is a CLAUSE, not a FUNCTION. And the DENSE_RANK* answer was almost right by not specifying if the ordering was done with the OVER clause or with an ORDER BY

* Of course, DENSE_RANK doesn't leave gaps in the ordering sequence...but I never pretended I knew golf





Robert Lomax
Robert Lomax
Say Hey Kid
Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)

Group: General Forum Members
Points: 694 Visits: 14
Please post the T-SQL example here.
MG-148046
MG-148046
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3771 Visits: 2828
The choice of words was, in truth, a poorly written spec. It left way too much room for assumption. My "assumption" was that if there was a 3 way tie for first place, the person(s) in second would not appreciate seeing the number 4 as a ranking since the rank, at that point, is actually 2.

MG

"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
keyser soze-308506
keyser soze-308506
SSC Veteran
SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)

Group: General Forum Members
Points: 231 Visits: 125

hey, next time, publish the query and/or the table columns for better understanding of the question.


Charles Wannall
Charles Wannall
SSC Veteran
SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)

Group: General Forum Members
Points: 238 Visits: 31

I disagree with the solution. A list of rankings should not have gaps, which RANK would leave. You could see a list such as 1, 2, 3, 3, 5, which makes no sense. It might be sufficient to determine the prizes, but the preferable list would be 1, 2, 3, 3, 4, which DENSE_RANK would yield.

As proof I submit the following data, T-SQL and results:

Player 1 Score 12
Player 2 Score 14
Player 3 Score 16
Player 4 Score 12
Player 5 Score 13
Player 6 Score 14
Player 7 Score 17

SELECT RANK() OVER (ORDER BY SCORE) As Rank
,[Score]
,
[Player]
FROM [ScratchPad].[dbo].[Scores]

Rank Score Player
1 12 4
1 12 1
3 13 5
4 14 6
4 14 2
6 16 3
7 17 7

SELECT DENSE_RANK() OVER (ORDER BY SCORE) As DenseRank
,
[Score]
,
[Player]
FROM [Scratch

DenseRank Score Player
1 12 4
1 12 1
2 13 5
3 14 6
3 14 2
4 16 3
5 17 7


Robert Lomax
Robert Lomax
Say Hey Kid
Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)

Group: General Forum Members
Points: 694 Visits: 14
In golf, the correct list would be 1, 2, 3, 3, 5. Nobody came in 4th place, but someone did come in 5th.
Yelena Varshal
Yelena Varshal
SSCrazy Eights
SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)

Group: General Forum Members
Points: 9054 Visits: 600

The question did not specify what did they want: consecutive places with 2 people sharing the first place, 3 people sharing the second place etc. or places with the gaps. Many people including me assumed they want consecutive places

Please explain how OVER with partitioning is applicable here, I don't think it is. We are not ranking inside partitions. Here is an explanation from BOL:

PARTITION BY

Divides the result set into partitions. The window function is applied to each partition separately and computation restarts for each partition.

I would reward points for Dense_Rank.

This is a BOL explanation for Dense_Rank

If two or more rows tie for a rank in the same partition, each tied rows receives the same rank. For example, if the two top salespeople have the same SalesYTD value, they are both ranked one. The salesperson with the next highest SalesYTD is ranked number two. This is one more than the number of distinct rows that come before this row. Therefore, the numbers returned by the DENSE_RANK function do not have gaps and always have consecutive ranks.




Regards,
Yelena Varshal

Larry Briscoe
Larry Briscoe
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2601 Visits: 99

I agree, you should also reward points for DENSE_RANK. It will do what the QOD asked for.

"Players who finish on the same number of strokes have to be given the same finishing place denoting that they will have an equal prize amount."

There is no mention of leaving gaps. DENSE_RANK will give the same rank as the question implies.

Don't penalize us because we don't play golf.

After all this is not sqlserverGOLFcentral.com. If it was I wouldn't be here!





Steve Jones
Steve Jones
SSC Guru
SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)

Group: Administrators
Points: 148458 Visits: 19444
How about if we change to sqlserverGUITARcentral.com

Points will be awarded for DENSE_RANK as well.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search