Re: Grouping by day, limiting amounts

am Thu, dem 19.10.2006, um 13:51:55 +0200 mailte Mezei Zoltán folgendes:
> 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?

I would write a Set-returning function. First, select distinct all
dates, and interate over this to select the names and number of votes
for this date, ordered by the votes descending and limit this to 3 oder
10 or whatever.

simplified:

create function .... (IN c int, OUT d date, OUT n text, OUT x int) returns setof record ...
declare
temp_date date;
rec record;
begin
for select into temp_date distinct date from table loop
for select into rec name, votes from table where datum=temp_date
order by 2 limit by $1 loop
n := rec.name;
...
return next
...

HTH, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net