Re: LIMIT and SUBQUERIES

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