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 [EMAIL PROTECTED] * andrew_perrin (at) unc.edu -- TriLUG mailing list : http://www.trilug.org/mailman/listinfo/trilug TriLUG Organizational FAQ : http://trilug.org/faq/ TriLUG Member Services FAQ : http://members.trilug.org/services_faq/ TriLUG PGP Keyring : http://trilug.org/~chrish/trilug.asc
