[TriLUG] Any SQL experts out there?

[Andrew Perrin]

This has got to be an easy question, but I"m not figuring out right - any
help would be most welcome.

I have a postgresql database that contains (among others) two tables: one
with information about research subjects (subjects), the other with
data coders have entered about the subjects' interviews (codes_assigned).
The relationship is one-to-many, with each subject having between 1 and 3
codes_assigned records.

For this round of analysis, I want to use the first-entered codes_assigned
record for each subject. I can return this information easily enough with:

SELECT partid, min(ca_date) FROM codes_assigned GROUP BY partid ORDER BY
partid;

(partid is the participant ID number, the field on which the two tables
can be joined.)

The problem is, I need to have a way to join the subjects table to the
actual data in the codes_assigned table, and when I try to include
information in the SELECT other than the GROUP BY field, I get an error:

leted=# select coder, ca_partid, tone_angry, min(ca_date) from
code_assignments group by ca_partid order by ca_partid;
ERROR:  column "code_assignments.coder" must appear in the GROUP BY clause
or be used in an aggregate function



I can hack this together in perl, but would prefer to learn the Right Way
(tm) instead. Any ideas?

Thanks,
Andy

----------------------------------------------------------------------
Andrew J Perrin - http://www.unc.edu/~aperrin
Assistant Professor of Sociology, U of North Carolina, Chapel Hill
clists at perrin.socsci.unc.edu * andrew_perrin (at) unc.edu