Message-ID: <3E651C0D.2040700@klaster.net>
Date: Tue, 04 Mar 2003 22:35:09 +0100
From: Tomasz Myrta <jasiek@klaster.net>
User-Agent: Mozilla/5.0 (Windows; U; Win 9x 4.90; PL; rv:1.1) Gecko/20020826
MIME-Version: 1.0
To: Chris <cprice@hrdenterprises.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: LIMIT and SUBQUERIES
References: <a1a26a5.0303031054.138057b1@posting.google.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Chris wrote:
> Hi all,
> 
> This question may be a bit confusing, and it is entirely possible that
> I am going about it the wrong way, but any suggestions would be much
> appreciated.  I'm trying to query a table of records that has a
> (simplified) structure like the following:
> 
> owner int
> description text
> amount double
> 
> I want to do a select that returns the TOP 5 records ordered by
> amount, PER OWNER.  I can easily construct this SQL query, the problem
> arises in the fact that I want to have groups of the top five per
> owner (an owner can obviously have more than 5 records, but I just
> want the top 5 for each).
> 
> So anyway, I have the query that is working - but it returns all
> records for all owners, when what I really want to do is return the
> top 5 per each owner.
> 
> Any suggestions?
> 
> Thanks
> Chris
It's not too easy to do this for large tables. If your table isn't too 
big, you can try this:

select
   t1.owner,
   t1.description,
   t1.amount
from
  some_table t1
  join some_table t2 using (owner)
where t2.amount<=t1.amount
group by t1.owner,t1.description,t1.amount
having count(*)<=5

In English:
"For each owner return these amounts, for which there are no more then 4 
smaller amounts"
This query is simple, but needs 0.5*amounts^2 calculations for each owner.

Regards,
Tomasz Myrta