Not a SQL standard, but many people think the functionality of GROUP BY
works the way the combination of BREAK ON and COMPUTE work, and this is
often a source of great confusion. I think what they really wish is that
GROUP BY worked that way!
"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
news:tv1e2v6tk8e315_at_corp.supernews.com...
>
> "Neil Zanella" <nzanella_at_cs.mun.ca> wrote in message
> news:b68d2f19.0111121632.5a3d765b_at_posting.google.com...
> > Nicholas Carey <ncarey_at_speakeasy.org> wrote in message
> news:<Xns9156F26955274ncareyspeakeasyorg_at_207.126.101.92>...
> > > On 10 Nov 2001, Neil Zanella <nzanella_at_garfield.cs.mun.ca>
> > > spake and said:
> > >
> > > >
> > > > Hello,
> > > >
> > > > The SQL standard imposes that when using the GROUP BY clause
> > > > all attributes appearing in the SELECT clause must also
> > > > appear in the GROUP BY clause. This design decision has its
> > > > good reasons for being there but there are cases in which
> > > > this is a bit of a hinderance.
> > >
> > > OK. I'll bite. The SQL standards mandates that all columns in the
> > > result set of a select statment that has GROUP BY in it are
> > > either (A) a constant, (B) an element of the GROUP BY list, or
> > > (C) an aggregate function operating on the group.
> > >
> > > You think that this is a problem. Why?
> > >
> > > Consider this table:
> > >
> > > A B
> > > -- --
> > > 1 A
> > > 1 A
> > > 2 A
> > > 2 B
> > > 2 C
> > > 3 C
> > > 3 D
> > >
> > > and this piece of trivial SQL:
> > >
> > > select a , b
> > > from foo
> > > group by a
> > >
> > > Assuming that the Standard's requirements for GROUP BY don't hold
> > > true for this example, what does column B return for any given
> > > group A? Bear in mind that GROUP BY summarizes, so that a single
> > > row is returned for each group.
> >
> > All I am saying is that the standard should be more flexible. In the
> > above example A is not the primary key of the given table thus for
> > each valuein
> > column A there can be multiple values for the corresponding element in
> > column B as you pointed out. However if A was a primary key then I see
> > no reason why the standard should not accept the above query. Thus the
> > standard should add to (A), (B), and (C) the following:
> > (D) any columns whose value is uniquely determined by those appearing
> > in
> > the GROUP BY clause for any instance of the given table. In
> > particular, if
> > the primary key appears in the GROUP BY clause then any column should
> > be
> > allowed in the SELECT clause.
> >
> > Regards,
> >
> > Neil
>
> IMO you simply just fail to see what GROUP BY is all about.
> It's _purpose_ is to compute sums, mins, maxs, counts, averages etc
> _NOT_ to accomplish what could have been done by a simple _ORDER BY_
because
> that's what you are confusing the GROUP BY with.
> Also you should keep in mind GROUP BY is an example why SQL should be
> criticised from the relational perspective. The content of a table is a
set.
> A set is by design unordered, and any elements are purposively unique, as
a
> set can't have nonunique elements. So, from the relational perspective,
> there is no such thing as a primary key, as it is unneeded.
> You are trying to add more 'intelligence' than can be allowed in the
> relational model.
>
> Regards
>
>
> --
> Sybrand Bakker
> Senior Oracle DBA
>
> to reply remove '-verwijderdit' from my e-mail address
>
>
>
Received on Tue Nov 13 2001 - 13:59:28 CST
