Re: Why using "Group By"
Date: Thu, 13 Mar 2003 16:13:06 -0500
"Mikito Harakiri" <mikharakiri_at_ywho.com> wrote in message
> "Bob Badour" <bbadour_at_golden.net> wrote in message
> > 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.
> > the statement gave an error instead, one could at least point out that
> > 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
> 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
> 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.
Not me. Received on Thu Mar 13 2003 - 22:13:06 CET