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(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)