Re: Newbie: Creative use of LIMIT??

On Wednesday 17 Jul 2002 3:20 am, Shmuel A. Kahn wrote:
> Assuming I have the following two tables of people and their nicknames,
> and that I want to create a list containing UPTO 2 (or any value
> greater than 1) nicknames for EACH member of a specific family (Fam
> column), how would I do this?
>
> but am totally clueless on how to impose a limit on the number of rows
> to get for each individual family MEMBER.

Something along these lines should be what you're after:

SELECT * FROM nicknames;
id | person_id | nick
----+-----------+--------------
1 | 1 | Darth
2 | 1 | Lord Vader
3 | 1 | Mr Black-hat
4 | 2 | Luke
5 | 2 | Metal-hand
6 | 2 | Bad-hair boy
(6 rows)

SELECT * FROM nicknames n1
WHERE n1.id IN (
SELECT n2.id FROM nicknames n2
WHERE n2.person_id=n1.person_id
ORDER BY id LIMIT 2
);
id | person_id | nick
----+-----------+------------
1 | 1 | Darth
2 | 1 | Lord Vader
4 | 2 | Luke
5 | 2 | Metal-hand
(4 rows)

Note that this is running a separate subquery for each person_id so if you
have a large table performance might not be brilliant. The usual advice is to
try to rewrite the IN as an EXISTS instead, but I'm not clear on how you'd do
that in this case.

Actually, looking at it, it might run a separate subquery for each row.
Ideally, there'd be some way of having a "PERGROUP LIMIT" imposed, but I'm
afraid I don't know of one.

- Richard Huxton