Re: GROUP BY

Lennart <erik.lennart.jonsson_at_gmail.com> wrote in news:f2q3er$62s$1_at_registered.motzarella.org:

> V.J. Kumar wrote:
> [...]
>>>
>>> which I imagine is the same as:
>>>
>>> select 1 from t1 group by ()
>>
>> In other implemmentations in accordance with the 2003 standard, you
>> will still obtain zero rows, either with 'group by ()' or 'group by
>> grouping sets (())'. Not surprising really.
>>
>
> I think I misread your first post. I thought you where questioning the
> syntax of group by () (AFAIK it was not valid in SQL92, but introduced
> in SQL99). Anyhow, I think I have the SQL2003 draft lying around some
> where, can you point me to where in the standard the semantics of
> group by () is defined?

See paragraph 7.9 (group-by). By a series of syntactic transformations, cube/rollup/grouping sets are reduced to a union of 'primitive' groupby' s. In particular, as you know, 'group by grouping sets (())' ==> 'group by ()'. Further, 'a) If there are no grouping columns, then the result of the <group by clause> is the grouped table consisting of T as its only group'. Clearly, if the table is empty so is the result of group by (). Woud be really odd if it were not so.

>I'm curios cause DB2 returns 1 row, and

Apparently, the IBM folks need to read the standard !

> apparently others return 0 rows. I can imagine both definitions, and
> would like to check out what the standard actually says.
>
>
> Thanks
> /Lennart
>
>
> [...]
>
Received on Mon May 21 2007 - 01:24:07 CEST