A reader asked if their query could list individuals and their scores in various competitions and rank them for the individual – a way to see that individual’s on-going performance.
But this query could easily be car sales by model by store or individual blood pressure readings for various units of a medical facility.
The more challenging part of the request was a way to break ties for an individual so that the ranking was sequential – that is, there were no duplicate rank values. Typically when there is a tie both entrants are given the same rank and the next rank values are skipped. In this example we break the tie by adding a tie breaker value using a SELECT subquery that returns a value added to the rank to break the tie.
I look for matching PlayerIDs and Scores and if found count the number of CompIDs that are less than the current CompID. Usually this results in 0 until a tie is found and then a value is returned that is added to the rank.
