return first three results of every group ( weekly top 3 ) in Postgresql

Question

 CREATE TABLE games
    (
      idg serial NOT NULL,
      nation character(3),
      points integer,
      datag date,
      CONSTRAINT pk_games PRIMARY KEY (idg )
)

idg    nation  points      dateg
1      ita      12      2011-10-10
2      fra       9      2011-10-11
3      ita       4      2011-10-12
4      fra       8      2011-10-11
5      ger      12      2011-10-12
6      aut       6      2011-10-10
7      ita      11      2011-10-17
8      ita      10      2011-10-18
9      fra       9      2011-10-19
10     ger      15      2011-10-19
11     fra      16      2011-10-18

I want to display the biggest three total grouped on weeks. I understand I can't use max(sum(points), so I made next query:

select extract(week from datag) as "dateg", nation, sum(points) as "total"
from games
group by dateg, nation
order by dateg asc, total desc limit 3

but these returns me just the first three totals. How can I made it for every week (the first three totals on every group, this will by a sort of "weekly top 3") ? Any ideea ?

Working in Postgresql 9.

Thanks in advance.

Answer 1

Use a window function:

select idg, nation, points, wk, r
from (
    select idg, nation, points, extract(week from datag) as wk,
           row_number() over (partition by extract(week from datag) order by points desc) as r
    from games
) as dt
where r <= 3

Adjust the SELECTs as desired. You can add nation to the ORDER BY inside the PARTITION if you want unique rankings.

And if you want to total the per-week points for each country first then you just add another derived table and adjust your column names a bit:

select nation, wk, wk_points, rn
from (
    select nation, wk, wk_points,
           row_number() over (partition by wk order by wk_points desc) as rn
    from (
        select nation, extract(week from datag) wk, sum(points) wk_points
        from games
        group by wk, nation
    ) as dt_sum
) as dt
where rn <= 3