From: Shmuel A. Kahn <Shmuel@Kam-motion.com>
To: pgsql-sql@postgresql.org
Subject: Newbie: Creative use of LIMIT??
Date: Wed, 17 Jul 2002 04:20:44 +0200
Message-ID: <3D34F09C.13692.1B5CF71@localhost> (raw)
Hello all,
Being pretty new at SQL, I have a problem I don't know how to solve.
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?
I know how to get ALL of the nicknames for the family:
SELECT "MEMBERS"."Name", "NICKS"."Nickname"
FROM "NICKS",
(SELECT "PEOPLE"."ID", "PEOPLE"."Name" FROM "PEOPLE"
WHERE "PEOPLE"."Fam" = 1) AS "MEMBERS"
WHERE "NICKS"."ID" = "MEMBERS"."ID"
ORDER BY "MEMBERS"."Name"
but am totally clueless on how to impose a limit on the number of rows
to get for each individual family MEMBER.
Can it be done in a single query? I sure hope so. Any help?
Shmuel A. Kahn
PEOPLE
Fam |ID | Name | Yada1 Yada2 ....
----------------------------------
1 | 1 | George .....
1 | 2 | Sally .....
1 | 3 | Joe .....
1 | 4 | Barbara .....
2 | 5 | DarthVader .....
.....
.....
NICKS
ID | Nickname
------------------
1 | Pops
1 | BigChief
1 | Honcho
2 | Mums
2 | YesMother
3 | JoeJoe
3 | Bumpkin
3 | Jug
4 | Barb
.....
.....
--
You're just jealous because the voices only talk to me.
Shmuel A. Kahn
Shmuel@Kam-motion.com
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: Shmuel@Kam-motion.com
Subject: Re: Newbie: Creative use of LIMIT??
In-Reply-To: <3D34F09C.13692.1B5CF71@localhost>
* 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