[TriLUG] OT: SQL "Group by" question

[Andrew Perrin]

On Tue, 3 Oct 2006, Barry Gaskins wrote:

>  Try this:
>
> select a1.assign_id, a1.isbn, a1.assign_date as last_assign_date
> from assign a1, assign a2
> where a1.isbn = a2.isbn
> and   a1.assign_date = max(a2.assign_date)
> group by a1.assign.isbn;
>
>

sf=# select a1.assign_id, a1.isbn, a1.assign_date as last_assign_date
sf-# from assign a1, assign a2
sf-# where a1.isbn = a2.isbn
sf-#  and   a1.assign_date = max(a2.assign_date)
sf-# group by a1.assign.isbn;
ERROR:  schema "a1" does not exist

----------------------------------------------------------------------
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 10/3/06, Andrew Perrin <clists at perrin.socsci.unc.edu> 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/
>> 
> -- 
> 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/
>