Thread: 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

On Monday 03 Mar 2003 6:52 pm, Chris wrote:
> 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).

richardh=# SELECT * FROM foo;
 id | a | b
----+---+----
  1 | a | 10
  2 | a | 11
  3 | a | 12
  4 | b |  5
  5 | b |  6
  6 | b |  7
(6 rows)

richardh=# SELECT * FROM foo WHERE foo.id IN
(SELECT f.id FROM foo f WHERE f.a=foo.a ORDER BY b DESC LIMIT 2);
 id | a | b
----+---+----
  2 | a | 11
  3 | a | 12
  5 | b |  6
  6 | b |  7
(4 rows)

This query may be slow however.

--
  Richard Huxton