[TriLUG] Any SQL experts out there?

[David Wagoner]

Not sure of your exact table/column structure, but have you tried some
flavor of this:

select s.coder, ca.ca_partid, ca.tone_angry, min(ca.ca_date)
from   code_assignments ca, subjects s
where  ca.partid = s.partid
group by s.coder, ca.ca_partid, ca.tone_angry
order by ca.ca_partid;

david


On Fri, 25 Feb 2005 13:52:59 -0500 (EST), Andrew Perrin
<clists at perrin.socsci.unc.edu> 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
> 
> --
> 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
>