Re: Why using "Group By"

"Mikito Harakiri" <mikharakiri_at_ywho.com> wrote in message news:F86ca.16$wV5.40_at_news.oracle.com...
>
> "Bob Badour" <bbadour_at_golden.net> wrote in message
> news:pS5ca.65$hR2.8462398_at_mantis.golden.net...
>
> > If you have access to sybase (or sqlserver probably), try a query like:
> >
> > select a, b, count(*) from sometable group by a
> >
> > They seem to treat the expression as some sort of request for a cross
> > product of sometable with itself just to tie up resources on the server.
> If
> > the statement gave an error instead, one could at least point out that
the
> > redundancy aids compile-time error detection.
>
> This result might be consistent with C.J.Date proposal to treat
>
> select a, b, count(*) from sometable group by a
>
> as
>
> select distinct a, b,
> (select count(*) from sometable as it where it.a = ot.a)
> from sometable as ot

I am unfamiliar with the proposal you mention, but that is not the result I recall getting in any case.

> For syntactically correct "group by" (when the select column list is a
> subset of "group by" list) rewritten the results are identical.

Except that the only purpose of this is to destroy logical identity. Is this as good as maintaining logical identity? I think not.

> Your case
> can be considered as a "generalized group by". This kind of output is
> routinely produced by "Window" aggregate functions. Are window analytical
> extensions legitimate from theoretical point of view?
>
> BTW, oracle rejects "group by" query where select list is inconsistent
with
> column list in the "group by" clause.

Good. Errors should be errors. I have never seen anyone write a query with an inconsistent group by that was not an error. Since a relation with distinct a & b values extended with a count of distinct a's already has several redundant syntactically correct formulations, I think we can safely reject the inconsistent group by as an error.

> Anybody tested DB2?

Not me. Received on Thu Mar 13 2003 - 22:13:06 CET