pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedNewbie: Creative use of LIMIT??
2+ messages / 2 participants
[nested] [flat]
* Newbie: Creative use of LIMIT??
@ 2002-07-17 02:20 Shmuel A. Kahn <Shmuel@Kam-motion.com>
2002-07-18 09:59 ` Re: Newbie: Creative use of LIMIT?? Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 2+ messages in thread
From: Shmuel A. Kahn @ 2002-07-17 02:20 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Newbie: Creative use of LIMIT??
2002-07-17 02:20 Newbie: Creative use of LIMIT?? Shmuel A. Kahn <Shmuel@Kam-motion.com>
@ 2002-07-18 09:59 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 2+ messages in thread
From: Richard Huxton @ 2002-07-18 09:59 UTC (permalink / raw)
To: Shmuel A. Kahn <Shmuel@Kam-motion.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2002-07-18 09:59 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-07-17 02:20 Newbie: Creative use of LIMIT?? Shmuel A. Kahn <Shmuel@Kam-motion.com>
2002-07-18 09:59 ` Richard Huxton <dev@archonet.com>
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