pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedLIMIT and SUBQUERIES
4+ messages / 4 participants
[nested] [flat]
* LIMIT and SUBQUERIES
@ 2003-03-03 18:54 Chris <cprice@hrdenterprises.com>
2003-03-04 21:35 ` Re: LIMIT and SUBQUERIES Tomasz Myrta <jasiek@klaster.net>
0 siblings, 1 reply; 4+ messages in thread
From: cprice@hrdenterprises.com @ 2003-03-03 18:54 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: LIMIT and SUBQUERIES
2003-03-03 18:54 LIMIT and SUBQUERIES Chris <cprice@hrdenterprises.com>
@ 2003-03-04 21:35 ` Tomasz Myrta <jasiek@klaster.net>
0 siblings, 0 replies; 4+ messages in thread
From: Tomasz Myrta @ 2003-03-04 21:35 UTC (permalink / raw)
To: Chris <cprice@hrdenterprises.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: LIMIT and SUBQUERIES
@ 2003-03-05 13:24 Christoph Haller <ch@rodos.fzk.de>
2003-03-05 15:22 ` Re: LIMIT and SUBQUERIES Greg Stark <gsstark@mit.edu>
0 siblings, 1 reply; 4+ messages in thread
From: Christoph Haller @ 2003-03-05 13:24 UTC (permalink / raw)
To: pgsql-sql; +Cc: cprice@hrdenterprises.com
>
> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: LIMIT and SUBQUERIES
2003-03-05 13:24 Re: LIMIT and SUBQUERIES Christoph Haller <ch@rodos.fzk.de>
@ 2003-03-05 15:22 ` Greg Stark <gsstark@mit.edu>
0 siblings, 0 replies; 4+ messages in thread
From: Greg Stark @ 2003-03-05 15:22 UTC (permalink / raw)
To: Christoph Haller <ch@rodos.fzk.de>; +Cc: pgsql-sql; cprice@hrdenterprises.com
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-03-05 15:22 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-03 18:54 LIMIT and SUBQUERIES Chris <cprice@hrdenterprises.com>
2003-03-04 21:35 ` Tomasz Myrta <jasiek@klaster.net>
2003-03-05 13:24 Re: LIMIT and SUBQUERIES Christoph Haller <ch@rodos.fzk.de>
2003-03-05 15:22 ` Greg Stark <gsstark@mit.edu>
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