[TriLUG] Any SQL experts out there?

[Ron Joffe]

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
> clists at perrin.socsci.unc.edu * 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