[TriLUG] OT: SQL "Group by" question

Cristobal Palmer cristobalpalmer at gmail.com
Tue Oct 3 14:26:30 EDT 2006


Okay, so my earlier answer was totally bogus.

For my own sanity (and not being beaten to death by my prof if he
reads this list)  I wanted to reply to this and point out:

http://www.ils.unc.edu/courses/2006_fall/inls623_001/Class_Teaching_Notes.htm

Specifically "Work Q27 and explain aggregates in GROUP BY versus
SELECT (i.e. in select if you use aggregate operation you can only use
aggregate operators; elsewise you'll need to nest. see Q27, or use a
GROUP BY)."

Where Q27 is "Find the name and age of the oldest sailor."

The incorrect first attempt the book[1] gives is:

SELECT S.sname, MAX(S.age)
FROM Sailors S

which fails for the reason given above. The solution they give is:

SELECT S.sname, S.age
FROM Sailors S
WHERE S.age = (SELECT MAX(S2.age)
                          FROM Sailors S2 );

Sorry for the extra traffic,

-CMP

[1] Ramakrishnan & Gehrke. Database Management Systems (third
edition).  Boston: McGraw Hill, 2003.

On 10/3/06, Thomas <thomasvt at gmail.com> 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/
>


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



More information about the TriLUG mailing list