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