Andrew,
If assing is not a many to many relation, why did you not fold accept_id into assign?
Any way, here is the query you need:
select assign.reviewer_id, ss.max_assign_date, accept.assign_id, accept.accept_id from ( select reviewer_id, max( assign_date) as max_assign_date from assign group by reviewer_id) as ss, assign, accept where ss.reviewer_id = assign.reviewer_id and ss.max_assign_date = assign.assign_date and assign.assign_id = accept.assign_id;
Andrew Perrin wrote:
I have a table of people ("reviewers"), a table of review assignments ("assign"), and a table of review acceptances ("accept"). I would like to be able to write a query to return the latest (e.g., max(assign_date)) assignment for each reviewer, plus the acc_id field from "accept". I think I should be able to do this with a GROUP BY clause, but am having no luck.
Table structure:
reviewers assign accept ----------------------------------------- reviewer_id assign_id accept_id reviewer_id assign_id ... assign_date ... ...
Thanks for any guidance.
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
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
