Re: Why using "Group By"

"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

For syntactically correct "group by" (when the select column list is a subset of "group by" list) rewritten the results are identical. 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. Anybody tested DB2? Received on Thu Mar 13 2003 - 21:42:34 CET