Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Question of the Day for 26 Apr 2006 Expand / Collapse
Author
Message
Posted Tuesday, April 25, 2006 12:05 PM
SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, May 18, 2007 3:36 PM
Points: 10,039, Visits: 1
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.
Post #275309
Posted Wednesday, April 26, 2006 6:12 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, October 08, 2013 3:54 PM
Points: 713, 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

 




Post #275473
Posted Wednesday, April 26, 2006 6:41 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Sunday, August 16, 2009 5:37 PM
Points: 614, Visits: 14
Please post the T-SQL example here.
Post #275482
Posted Wednesday, April 26, 2006 6:43 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, April 14, 2014 6:27 AM
Points: 1,757, Visits: 2,118
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.

Post #275483
Posted Wednesday, April 26, 2006 7:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 3:03 PM
Points: 163, Visits: 113

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

Post #275500
Posted Wednesday, April 26, 2006 8:23 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, August 25, 2013 4:41 PM
Points: 93, Visits: 30

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

Post #275537
Posted Wednesday, April 26, 2006 9:35 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Sunday, August 16, 2009 5:37 PM
Points: 614, 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.
Post #275590
Posted Wednesday, April 26, 2006 9:58 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, December 26, 2013 9:45 AM
Points: 3,475, Visits: 577

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

Post #275611
Posted Wednesday, April 26, 2006 10:26 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, March 17, 2014 12:38 PM
Points: 2,577, Visits: 95

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!




Post #275621
Posted Thursday, April 27, 2006 3:06 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:31 PM
Points: 32,780, Visits: 14,941
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
Post #276041
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse