From: Tomasz Myrta <jasiek@klaster.net>
To: Chris <cprice@hrdenterprises.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: LIMIT and SUBQUERIES
Date: Tue, 04 Mar 2003 22:35:09 +0100
Message-ID: <3E651C0D.2040700@klaster.net> (raw)
References: <a1a26a5.0303031054.138057b1@posting.google.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jasiek@klaster.net, cprice@hrdenterprises.com
Subject: Re: LIMIT and SUBQUERIES
In-Reply-To: <3E651C0D.2040700@klaster.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox