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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
