Обсуждение: LIMIT and SUBQUERIES

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

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

>
> 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.
>
I've seen a lot of questions like this on the list before about
filtering
result sets. Within the most replies, people were told to use middleware

stuff like perl, awk, sed, ... to filter out unwanted rows, because SQL
was not intended to do so.
Incidentally, I've seen

http://techdocs.postgresql.org/guides/SetReturningFunctions

Maybe this is more useful than telling you 'use a script'.

Regards, Christoph

Christoph Haller <ch@rodos.fzk.de> writes:

> >
> > 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.
> >
> I've seen a lot of questions like this on the list before about
> filtering
> result sets. Within the most replies, people were told to use middleware
> 
> stuff like perl, awk, sed, ... to filter out unwanted rows, because SQL
> was not intended to do so.

This is the same problem as the "ranking" problem that I mentioned earlier.

http://archives.postgresql.org/pgsql-sql/2003-03/msg00013.php

Essentially we would need a kind of feature that has some similarities to user
defined aggregates but is not exactly the same thing. The feature doesn't
currently exist, and I haven't seen it in order databases, so I don't even
know exactly what it would look like.

-- 
greg