Okay, so my earlier answer was totally bogus. For my own sanity (and not being beaten to death by my prof if he reads this list) I wanted to reply to this and point out:
http://www.ils.unc.edu/courses/2006_fall/inls623_001/Class_Teaching_Notes.htm Specifically "Work Q27 and explain aggregates in GROUP BY versus SELECT (i.e. in select if you use aggregate operation you can only use aggregate operators; elsewise you'll need to nest. see Q27, or use a GROUP BY)." Where Q27 is "Find the name and age of the oldest sailor." The incorrect first attempt the book[1] gives is: SELECT S.sname, MAX(S.age) FROM Sailors S which fails for the reason given above. The solution they give is: SELECT S.sname, S.age FROM Sailors S WHERE S.age = (SELECT MAX(S2.age) FROM Sailors S2 ); Sorry for the extra traffic, -CMP [1] Ramakrishnan & Gehrke. Database Management Systems (third edition). Boston: McGraw Hill, 2003. On 10/3/06, Thomas <[EMAIL PROTECTED]> wrote:
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/
-- 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/
