Thread: Newbie: Creative use of LIMIT??

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 | Pops1 | BigChief1 | Honcho2 | Mums2 | YesMother3 | JoeJoe3 | Bumpkin3 | Jug4 | Barb
.....
.....
--
You're just jealous because the voices only talk to me.
Shmuel A. Kahn
Shmuel@Kam-motion.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
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