Re: GROUP BY
Date: 19 May 2007 15:04:56 -0700
Message-ID: <1179612296.737601.145010_at_l77g2000hsb.googlegroups.com>
On 19 May, 15:20, "V.J. Kumar" <vjkm..._at_gmail.com> wrote:
My bad. Microsoft SQL Server 2005 doesn't support the empty grouping
set so what I actually tested was the following query:
CREATE TABLE r (b INT);
>
> > SELECT 1
> > FROM r
> > GROUP BY ();
>
> > returns a single tuple even if r is empty.
>
> What SQL would that be? Oracle SQL returns zero rows (as it is supposed
> to according to '92 standard), MS SQL Express does not even understand
> the expression and returns a syntax error.
>
According to ISO/IEC 9075-2:2003 section 7.10 the HAVING query without GROUP BY is equivalent to:
SELECT 1 FROM r GROUP BY () HAVING 1=1;
Microsoft SQL Server returns 1 row for the above, but this is wrong. When I test the same query in MySQL 5.0, Mimer SQL 9.2 and Oracle 10g all three products return zero rows.
-- David PortasReceived on Sun May 20 2007 - 00:04:56 CEST