I'm ranking race data for series of cycling events. Racers win various amounts of points for their position in races. I want to retain the discrete event scoring, but also rank the racer in the series. For example, considering a sub-query that returns this:

You can see Joe has 25 points, as he won 5, 12, and 8 points in three races. Ahmed has 20 points, as he won 12 and 8 points in two races.

Now for the ranking, what I'd like is:

But if I use rank() and order by "Total Points", I get:

Which makes sense, since there are three "ties" at 25 points.

dense_rank() solves this problem, but if there are legitimate ties across different racers, I want there to be gaps in the rank (e.g if Joe and Ahmed both had 25 points, the next racer would be in third place, not second).

The easiest way to solve this I think would be to issue two queries, one with the "duplicate" racers eliminated, and then a second one where I can retain the individual race data, which I need for the points break down display.

I can also probably, given enough effort, think of a way to do this in a single query, but I'm wondering if I'm not just missing something really obvious that could accomplish this in a single, relatively simple query.

Any suggestions?

CodePudding user response：

You have to break this into steps to get what you want, but that can be done in a single query with common table expressions:

with riders as (      -- get individual riders
  select distinct license, rider, total_points
    from racists
), places as (        -- calculate non-dense rankings
  select license, rider, rank() over (order by total_points desc) as place
    from riders
)
select p.place, r.*   -- join rankings into main table
  from places p
       join racists r on (r.license, r.rider) = (p.license, p.rider);

db<>fiddle here