On Friday 25 February 2005 13:52, Andrew Perrin wrote: > 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
How about a simple example of the data in the two tables, and the results you would like to see. Then let's see if we can help out. Ron -- 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
