PostgreSQL ranking query

Question

I am trying to do something similar to this but with the extra complication that I need to group by a common field

I have two tables, one for competition_users (for those competing in competitions) and another competition_times (to store their fastest times), but there are many different competitions in the same table.

I have created a new column rank in the competition_times table and now need to run an update for existing users to show their rank in their competition, I am trying to modify the solution given in the other SO answer without success

The tables are as follows...

competition_users:

competition_user_id, competition_id

competition_times:

competition_time_id, competition_user_id, time_in_seconds, rank

I am not sure if its possible to use GROUP BY? or if there is another way, so far I am trying something like this...

UPDATE competition_times
SET    rank = r.rnk
FROM (
    SELECT competition_time_id
         , dense_rank() OVER (ORDER BY time_in_seconds ASC) AS rnk
    FROM competition_times, competition_users
    WHERE competition_times.competition_user_id = competition_users.competition_user_id
    GROUP BY competition_users.competition_id
    ) r
WHERE competition_times.competition_time_id = r.competition_time_id

Using postgreSQL 9

Thanks

Is there supposed to be a `rank` field on the `competition_users` table as well (which is what your SQL suggests)? — tomato, Feb 27 '12 at 20:12
@tomtheguvnor no, sorry that was a copy & paste error, the rank is against the competition time (as this is a point-in-time snapshot) — DaveB, Feb 27 '12 at 20:44
@a_horse_with_no_name my problem is that I can get the query tp provide a rank by competition...the example I provided doesnt work — DaveB, Feb 27 '12 at 20:45
@DaveB: "*doesn't work*" is not a valid PostgreSQL error message — , Feb 27 '12 at 20:56
@a_horse_with_no_name ok but I knew the example SQL was a red herring, I just included it to show I had made some attempt at a solution — DaveB, Feb 28 '12 at 08:29

Gary - Stand with Ukraine · Accepted Answer · 2012-02-27T20:57:07.113

You need to use the PARTITION BY clause in the window function to keep the ranks separate between the competitions.

UPDATE competition_times
SET    
    rank = r.rnk
FROM (
    SELECT competition_time_id, dense_rank() OVER (PARTITION BY competition_id ORDER BY time_in_seconds ASC) AS rnk
    FROM competition_times
    INNER JOIN competition_users
      ON competition_times.competition_user_id = competition_users.competition_user_id
    ) r
WHERE competition_times.competition_time_id = r.competition_time_id

With the sample data below:

create table competition_users
(competition_user_id  int, competition_id   int);

create table competition_times
(competition_time_id int, competition_user_id int, time_in_seconds int, rank int);

insert into competition_users values 
(1,1),(2,1),(3,1),(4,1),(5,2),(6,2);

insert into competition_times values 
(1,1,10,null),
(2,2,20,null),
(3,3,15,null),
(4,4,15,null),
(5,5,10,null),
(6,6,7,null);

I get the results in competition_times of:

competition_time_id competition_user_id time_in_seconds rank
1                   1                   10              1
2                   2                   20              3
3                   3                   15              2
4                   4                   15              2
5                   5                   10              2
6                   6                    7              1

Where rows 1-4 are on one competition, and rows 5&6 are from another.

Fantastic, thanks Gary that works a treat, I was not aware of Partition By clause — DaveB, Feb 28 '12 at 08:27

PostgreSQL ranking query

1 Answers1