OK, I'll simplify the data as much as possible. Table "subjects": partid varchar(6) age smallint
partid | age ---------------------------------- W11111 | 15 W22222 | 25 ... W99999 | 34 Table "code_assignments": partid varchar(6) coder varchar(3) ca_date date tone_angry boolean partid | coder | ca_date | tone_angry ------------------------------------------------------------- W11111 | ajp | 2004-11-10 | t W11111 | lmh | 2004-10-18 | f W11111 | kmk | 2005-1-15 | t W22222 | ajp | 2004-11-10 | t ... W99999 | lmh | 2005-01-15 | f W99999 | kmk | 2005-01-10 | t I would like to return rows that look like: partid | age | coder | ca_date | tone_angry ---------------------------------------------------------------------- W11111 | 15 | lmh | 2004-10-18 | f W22222 | 25 | ajp | 2004-11-10 | t W99999 | 34 | kmk | 2005-01-10 | t such that data from "subjects" is joined with the *oldest* entry in code_assignments for the relevant partid. 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 On Fri, 25 Feb 2005, Ron Joffe wrote: > 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 > -- 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
