[TriLUG] Any SQL experts out there?

[Andrew Perrin]

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