Date: Thu, 19 Oct 2006 14:30:19 +0200
From: "A. Kretschmer" <andreas.kretschmer@schollglas.com>
To: pgsql-sql@postgresql.org
Subject: Re: Grouping by day, limiting amounts
Message-ID: <20061019123018.GC10803@a-kretschmer.de>
References: <453766DB.6000408@telefor.hu>
Mime-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <453766DB.6000408@telefor.hu>
User-Agent: Mutt/1.5.9i

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