This works in Oracle, but I'm not sure about postgressql,
select assign_id, isbn, assign_date from assign where (isbn, assign_date) in (select isbn, max(assign_date) from assign group by isbn); On 10/3/06, Andrew Perrin <[EMAIL PROTECTED]> wrote:
Yes, I understand it in that direction. It's the other direction I'm concerned about - I would like not just the date and isbn, but also the assign_id of the record returned by the max(assign_date) aggregate. Thanks, A ---------------------------------------------------------------------- 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 On Tue, 3 Oct 2006, Cristobal Palmer wrote: > You /have/ to have the column you want to group by in the select > statement. How can you group by values in a column that you didn't > retrieve? You have to have those values. > > The prof in my database class beat us over the head with this one. If > you want a more detailed answer I can dig up the class notes on that. > > -CMP > > 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/ >> > > > -- > Cristobal M. Palmer > UNC-CH SILS Student -- ils.unc.edu/~cmpalmer > TriLUG Vice Chair > "There are many roads to enlightenment, and thus many roads back to > the One True Debian" --crimsun > -- > 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/
-- 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/
