[TriLUG] OT: SQL "Group by" question

[Andrew Perrin]

Sorry for the slightly OT post, but my soft-science mind is having trouble 
wrapping itself around an SQL problem.

I have a table of book-review assignments, including a unique identifier 
(assign_id); the book's isbn (isbn); and the date we made the assignment 
(assign_date). There are often numerous assignments per isbn. I would like 
to retrieve the assign_id of the *latest* assignment per isbn.  I can get 
this far:

select assign.isbn, max(assign.assign_date) as last_assign_date from 
assign group by assign.isbn;

...which will give me the isbn and latest assign date, but of course I 
can't just ask for the assign_id in the way that seems obvious to me:

select assign.assign_date, assign.isbn, max(assign.assign_date) as 
last_assign_date from assign group by assign.isbn;

because assign_date isn't in the GROUP BY clause.

I can't imagine there isn't an accepted answer to this - any advice? I am, 
by the way, using postgresql 8.1 on debian linux.

Thanks,
Andy

----------------------------------------------------------------------
Andrew J Perrin - andrew_perrin (at) unc.edu - http://perrin.socsci.unc.edu
Assistant Professor of Sociology; Book Review Editor, _Social Forces_
University of North Carolina - CB#3210, Chapel Hill, NC 27599-3210 USA
New Book: http://www.press.uchicago.edu/cgi-bin/hfs.cgi/00/178592.ctl