This is untested, but it might be enough to get you started:
SELECT namecounter
FROM name n
WHERE NOT EXISTS (
SELECT 1
FROM name
WHERE hh > 0
AND famnu = n.famnu
)
GROUP BY famnu
HAVING birthdate = min( birthdate );What I'm trying to do here is grab all families that don't have a head of household, group them by family, and get only the namecounter corresponding to the minimum birthdate for that family.
If I recall, I've had some trouble using HAVING with min/max in ways that seem intuitive to me, but this might help get you started.
-tfo
On Oct 9, 2004, at 3:39 PM, Andrew Ward wrote:
I'm trying to figure out how to do a particular query, and I'm beating my head against a wall. Here's my situation:
I'm running postgres 7.3.2 on linux, and making my requests from Perl scripts using DBD::Pg. My table structure is as follows (irrelevant cols removed)
CREATE TABLE name ( namecounter integer NOT NULL, firstmiddle character varying(64) NOT NULL, lastname character varying(64) NOT NULL, birthdate date, hh smallint, famnu integer, );
Each row represents a person with a unique namecounter. Families share a famnu, and usually one person in a family is marked as head of household (hh>0), with everyone else hh=0. However, there are a few families with nobody marked as hh, and I'd like to elect one by age. The query I'm trying to do is to pull one person from each household, either the head of household if available, or the eldest if not. I want them sorted by last name, so I'd prefer to find them all in one query, no matter how ugly and nested it has to be.
I can pull the list with hh>0 easily enough, but I'm not sure how to pull out the others.
I realize that this could be done through some looping in the Perl script, but I'd like to avoid pulling the whole list into memory in case the list gets long. My preference is to just handle one record at a time in Perl if possible.
Help?
Andrew Ward [EMAIL PROTECTED]
__________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
