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/
