Re: Grouping by day, limiting amounts

On Thu, Oct 19, 2006 at 01:51:55PM +0200, Mezei Zolt??n wrote:
> 
> Hi,
> I didn't really know what subject I should give.
> I have a table like this one:
> 2006.10.01.    Bela    10
> 2006.10.01.    Aladar    9
> 2006.10.01.    Cecil    8
> 2006.10.01.    Dezso    7
> 2006.10.01.    Elemer    6
> 2006.10.02.    Bela    11
> 2006.10.02.    Aladar    10
> 2006.10.02.    Cecil    9
> 2006.10.02.    Dezso    8
> 2006.10.02.    Ferenc    7
> 2006.10.03.    Bela    6
> 2006.10.03.    Aladar    5
> 2006.10.03.    Cecil    4
> 2006.10.03.    Dezso    3
> 2006.10.03.    Jozef    2
> The first column is a date, the second is a name, the third is the
> number of votes that the name received on that day.
> I would like to select the 3 (or 10) names with the most votes for
> each day.
> Any suggestions on how can it be done easily?

It'd be easy with windowing functions, but unfortunately we don't have
those...

SELECT *   FROM (SELECT DISTINCT date FROM table) AS dates       , (SELECT date, name, votes               FROM table
           WHERE table.date = dates.date               ORDER BY votes DESC               LIMIT 3       )
 
;

Note that this has to scan the table twice (well, the second subquery
will likely use an index on date). If you have another table that has
the dates in it already, you can use that instead of the first subquery.
If you know that every day has a row, you could also replace the first
subquery with a generate_series().
-- 
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)