Content-Type: text/plain;
  charset="iso-8859-1"
From: Richard Huxton <dev@archonet.com>
Organization: Archonet Ltd
To: "Shmuel A. Kahn" <Shmuel@Kam-motion.com>,
	pgsql-sql@postgresql.org
Subject: Re: Newbie: Creative use of LIMIT??
Date: Thu, 18 Jul 2002 10:59:33 +0100
User-Agent: KMail/1.4.1
References: <3D34F09C.13692.1B5CF71@localhost>
In-Reply-To: <3D34F09C.13692.1B5CF71@localhost>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200207181059.33116.dev@archonet.com>

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=20
WHERE n1.id IN (
  SELECT n2.id FROM nicknames n2=20
  WHERE n2.person_id=3Dn1.person_id=20
  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=
=20
have a large table performance might not be brilliant. The usual advice is =
to=20
try to rewrite the IN as an EXISTS instead, but I'm not clear on how you'd =
do=20
that in this case.

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

- Richard Huxton