From: Andrew Ward <adward555@yahoo.com>
To: pgsql-sql@postgresql.org
Subject: SQL confusion
Date: Sat, 9 Oct 2004 13:39:45 -0700 (PDT)
Message-ID: <20041009203945.33114.qmail@web51609.mail.yahoo.com> (raw)
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
adward55@yahoo.com
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.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: adward555@yahoo.com
Subject: Re: SQL confusion
In-Reply-To: <20041009203945.33114.qmail@web51609.mail.yahoo.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