Re: [SQL] LIMIT and SUBQUERIES

[Tomasz Myrta]

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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly