Try this: select a1.assign_id, a1.isbn, a1.assign_date as last_assign_date from assign a1, assign a2 where a1.isbn = a2.isbn and a1.assign_date = max(a2.assign_date) group by a1.assign.isbn;
On 10/3/06, Andrew Perrin <[EMAIL PROTECTED]> wrote:
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 -- 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 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/
