Re: Group By Questions..

Connor McDonald mentioned that the data returned, ignoring order, would not be different and it occurred to me that perhaps the original question assumed that a different ordering does not imply a different result.

Intuitively, at least, a group by clause partitions the three columns, viewed as triplets, into subsets where each element of the set has their first, second and third components equal. If this is the case, then as long as the columns are not null the results should be identical. This seems to be what Tom Verhagen was referring to when he claimed that there would be no difference if the three columns were part of the primary key.

However, if the columns are permitted to be null, can a different grouping arise?

If the nulls are consistently considered either equal or not equal, then the results should still be the same, ignoring order. Oracle appears to consider null to equal null when constructing the groups as shown below:

The following table contains nulls in one, two or all three columns:

SQL> select * from jh;

        A B C D --------- --------- --------- ---------

        1         2         2
        1         2         2
        1         2
        1         2
        1
        1

If a null is equal to a null in forming the groups then there should be three rows returned. If nulls are not equal to nulls then each of the rows in jh should be in their own group.

SQL> select b,c,d, avg(a) from jh group by c,b,d;

        B C D AVG(A) --------- --------- --------- ---------

        2         2                   1
        2                             1
                                      1

As the query shows, a null is considered equal to a null in constructing the groups, so the results, ignoring order, will be identical.

Frank Hubeny

Jimmy wrote:

> Hello all,
>
> Assume I have a table with column A, B, C and D.
>
> I wonder the following SQL statements will produce the same results:
>
> i) select B, C, D, avg(A) from AAA group by B, C, D;
> ii) select B, C, D, avg(A) from AAA group by B, D, C;
> iii) select B, C, D, avg(A) from AAA group by C, B, D;
> ....
>
> i.e. does the order of group by clause affect the final result (or
> performance, if any) of the SQL statement? Anyone knows white papers or
> documents discussing this issue?
>
> Thanks,
> Jimmy
Received on Sat Oct 02 1999 - 08:28:21 CDT