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
hey, next time, publish the query and/or the table columns for better understanding of the question.
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:
SELECT RANK() OVER (ORDER BY SCORE) As Rank,[Score],[Player]FROM [ScratchPad].[dbo].[Scores]
Rank Score Player1 12 41 12 13 13 54 14 64 14 26 16 37 17 7
SELECT DENSE_RANK() OVER (ORDER BY SCORE) As DenseRank,[Score],[Player]FROM [Scratch
DenseRank Score Player1 12 41 12 12 13 53 14 63 14 24 16 35 17 7
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:
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.
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!
>>In golf, the correct list would be 1, 2, 3, 3, 5. Nobody came in 4th place, but someone did come in 5th.
By the way, thanks, Robert. I never knew this.