[TriLUG] OT: SQL "Group by" question

[Chander Ganesan]

Andrew Perrin 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.
Assuming the assign_id is generated using a sequence (the most logical 
way to generate a unique identifier), the latest assignment for an ISBN 
would also be the assignment with the highest assign_id for that ISBN.  
As a result, you can use the query below to get the latest assign_id for 
each ISBN for each assignment.

select a.isbn, max(a.assign_id) from assign a group by a.isbn;

Much simpler (and faster) that using a subquery.....

-- 
Chander Ganesan
The Open Technology Group - Expert PostgreSQL Training
One Copley Parkway, Suite 210
Morrisville, NC  27560
Phone: 877-258-8987/919-463-0999
http://www.otg-nc.com



>
> 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
>
>