Date: Thu, 19 Oct 2006 11:55:29 -0500
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
Message-ID: <20061019165528.GI71084@nasby.net>
References: <453766DB.6000408@telefor.hu>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <453766DB.6000408@telefor.hu>
User-Agent: Mutt/1.5.13 (2006-08-11)

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)