[TriLUG] OT: SQL "Group by" question

[Andrew Perrin]

Bingo! That did it. Thanks, all.

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



On Tue, 3 Oct 2006, Thomas 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 <clists at perrin.socsci.unc.edu> 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 <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/
>> >>
>> >
>> >
>> > --
>> > 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/
>