From: Jim C. Nasby <jim@nasby.net>
To: Mezei Zolt??n <mezei.zoltan@telefor.hu>
Cc: pgsql-sql@postgresql.org
Subject: Re: Grouping by day, limiting amounts
Date: Thu, 19 Oct 2006 11:55:29 -0500
Message-ID: <20061019165528.GI71084@nasby.net> (raw)
In-Reply-To: <453766DB.6000408@telefor.hu>
References: <453766DB.6000408@telefor.hu>
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@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
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: jim@nasby.net, mezei.zoltan@telefor.hu
Subject: Re: Grouping by day, limiting amounts
In-Reply-To: <20061019165528.GI71084@nasby.net>
* 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