From: A. Kretschmer <andreas.kretschmer@schollglas.com>
To: pgsql-sql@postgresql.org
Subject: Re: Grouping by day, limiting amounts
Date: Thu, 19 Oct 2006 14:30:19 +0200
Message-ID: <20061019123018.GC10803@a-kretschmer.de> (raw)
In-Reply-To: <453766DB.6000408@telefor.hu>
References: <453766DB.6000408@telefor.hu>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: andreas.kretschmer@schollglas.com
Subject: Re: Grouping by day, limiting amounts
In-Reply-To: <20061019123018.GC10803@a-kretschmer.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox