From: Richard Huxton <dev@archonet.com>
To: Shmuel A. Kahn <Shmuel@Kam-motion.com>
To: pgsql-sql@postgresql.org
Subject: Re: Newbie: Creative use of LIMIT??
Date: Thu, 18 Jul 2002 10:59:33 +0100
Message-ID: <200207181059.33116.dev@archonet.com> (raw)
In-Reply-To: <3D34F09C.13692.1B5CF71@localhost>
References: <3D34F09C.13692.1B5CF71@localhost>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, Shmuel@Kam-motion.com
Subject: Re: Newbie: Creative use of LIMIT??
In-Reply-To: <200207181059.33116.dev@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox