Re: LIMIT and SUBQUERIES

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