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
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: Administrators
Points: 18571 Visits: 222
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
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1135 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
SSC Eights!
SSC Eights! (904 reputation)SSC Eights! (904 reputation)SSC Eights! (904 reputation)SSC Eights! (904 reputation)SSC Eights! (904 reputation)SSC Eights! (904 reputation)SSC Eights! (904 reputation)SSC Eights! (904 reputation)

Group: General Forum Members
Points: 904 Visits: 14
Please post the T-SQL example here.
MG-148046
MG-148046
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6284 Visits: 2955
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
Old Hand
Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)

Group: General Forum Members
Points: 315 Visits: 132

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


Charles Wannall
Charles Wannall
Mr or Mrs. 500
Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)

Group: General Forum Members
Points: 506 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
SSC Eights!
SSC Eights! (904 reputation)SSC Eights! (904 reputation)SSC Eights! (904 reputation)SSC Eights! (904 reputation)SSC Eights! (904 reputation)SSC Eights! (904 reputation)SSC Eights! (904 reputation)SSC Eights! (904 reputation)

Group: General Forum Members
Points: 904 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
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18682 Visits: 608

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: 2621 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 (328K reputation)SSC Guru (328K reputation)SSC Guru (328K reputation)SSC Guru (328K reputation)SSC Guru (328K reputation)SSC Guru (328K reputation)SSC Guru (328K reputation)SSC Guru (328K reputation)

Group: Administrators
Points: 328980 Visits: 20109
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