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