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