Re: SQL confusion

On Sat, Oct 09, 2004 at 01:39:45PM -0700, Andrew Ward wrote:
>
> 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.

This should be close to what you need:

SELECT DISTINCT ON (lastname, famnu) *
FROM name
ORDER BY lastname, famnu, COALESCE(hh, 0) DESC, birthdate;

The order of the fields in the ORDER BY clause is important, so if
you want to sort the results by some other criteria then you might
need to do it with a subselect.

I used COALESCE on the hh field because a descending sort puts NULL
values ahead of non-NULL values -- without COALESCE a person with
hh=1 won't be recognized as the head of household if another member
of the family has hh=NULL.

You might need to modify the query to handle NULL birthdates or to
add other tiebreakers.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/