Counting number of ties after Rank() Function

Question

Counting number of ties after Rank() Function

pixelwiz

Right there with Babe

Points: 738
More actions
June 22, 2010 at 10:40 am

#224125

Hi,
I'm dealing with having to write some intense queries to do some golf statistics. My current challenge is two fold.
First, I have to display the top 10 players in each category, such as fewest putts, most fairway hits, etc. But it doesn't just stop there, I also have to display their position within the list such as T3 (if tied for third). I know how to use Rank() OVER (ORDER BY fairways_hit) to get people ordered correctly and figure out their position in the list. But, I also need to know when to display the T for ties. So what I really need to add is a count of people tied at that rank.
And no matter what I try, I can't get the syntax right.
Can anyone tell me how to do a count on a sub-query that has the Rank() value calculated to determine how many people are tied for a specific rank and display it along with all the other values in the query? (I know how to just to the count for how many are tied at each value, but can't figure out how to get it all at the same time)
Thanks much

Viewing 4 posts - 1 through 3 (of 3 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

You could use your current query as a subquery/CTE and use ROW_NUMBER() OVER(PARTITION BY rank_result_column ORDER BY order_criteria).

If this "hint" doesn't provide the help you need please post table DDL, ready to use sample data, your current query and your expected result based on the first article linked in my signature.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

pixelwiz Right there with Babe Points: 738 More actions · Answer 2

Hello,

Thanks for the suggestion, that's a very neat trick, however the result is not exactly what I was looking for.

So let's say there are 3 people tied for 4th place on a specific stat. What this did, was add a row number from 1-3 to each of those rows. While that can be useful, what I actually need is a count of how many total people are tied for that rank, in this case 3, added to each row as "ties" column. That way when displaying the output I'd know to display a T3 in front of each when there ties > 1.

Here is the top portion of the query, hopefully it will be sufficient to see what I'm trying to do, because the whole thing is kinda huge

SELECT ROW_NUMBER() OVER (PARTITION BY rank_fairways ORDER BY pct_fairways) cnt, * FROM (

SELECT '' display_pos,

RANK() OVER (ORDER BY pct_fairways desc) rank_fairways,

RANK() OVER (ORDER BY pct_regulationGreens desc) rank_regulationGreens,

RANK() OVER (ORDER BY pct_sandsaves desc) rank_sandsaves,

RANK() OVER (ORDER BY putts_per_GIR) rank_putts_per_GIR,

RANK() OVER (ORDER BY total_putts) rank_total_putts, * FROM (

SELECT (sub1.total_fairways/NULLIF(CAST(sub1.attempted_fairways AS DECIMAL),0)*100) pct_fairways,

(sub1.total_regulationGreens/NULLIF(CAST(sub1.attempted_regulationGreens AS DECIMAL),0)*100) pct_regulationGreens,

(sub1.total_sandsaves/NULLIF(CAST(sub1.attempted_sandsaves AS DECIMAL),0)*100) pct_sandsaves,

(sub1.total_GIRputts/NULLIF(CAST(sub1.total_regulationGreens AS DECIMAL),0)) putts_per_GIR, *

pixelwiz Right there with Babe Points: 738 More actions · Answer 3

Actually instead of Row_Number, doing this totally worked:

SELECT COUNT(rank_fairways) OVER (PARTITION BY rank_fairways) fairway_ties, * FROM (