SQL Snippets ™: Tutorials for Oracle Developers

All other things being equal, changing the order in which columns appear in the GROUP BY clause has no effect on the way the result set is grouped. For example, this query

select grp_a, grp_b, count(*)
from   t
GROUP BY GRP_A, GRP_B
order by grp_a, grp_b ;
 

GRP_A      GRP_B        COUNT(*)
---------- ---------- ----------
a1         b1                  2
a1         b2                  3
a2         b3                  3
 

returns the same results as this one.

select grp_a, grp_b, count(*)
from   t
GROUP BY GRP_B, GRP_A  -- columns have been reversed
order by grp_a, grp_b ;
 

GRP_A      GRP_B        COUNT(*)
---------- ---------- ----------
a1         b1                  2
a1         b2                  3
a2         b3                  3
 

Gotcha: GROUP BY with no ORDER BY

The last two snippets used the same ORDER BY clause in both queries. What happens if we use no ORDER BY clause at all?

select grp_a, grp_b, count(*)
from   t
group by grp_a, grp_b ;

GRP_A  GRP_B    COUNT(*)
------ ------ ----------
a1     b1              2
a1     b2              3
a2     b3              3

 

The results are still ordered. Some programmers interpret this as meaning that GROUP BY returns an ordered result set. This is an illusion which is easily proved with the following snippet. Note how the same query now returns rows in a random order given new conditions.

truncate table t;

-- this time we insert rows into T using a different order from that
-- of the Setup topic

insert into t values ( 'a2' , 'b3' , 'c2', 'd2', '32' ) ;
insert into t values ( 'a2' , 'b3' , 'c2', 'd2', '22' ) ;
insert into t values ( 'a2' , 'b3' , 'c2', 'd2', '12' ) ;
insert into t values ( 'a1' , 'b2' , 'c2', 'd1', '50' ) ;
insert into t values ( 'a1' , 'b2' , 'c1', 'd1', '40' ) ;
insert into t values ( 'a1' , 'b2' , 'c1', 'd1', '30' ) ;
insert into t values ( 'a1' , 'b1' , 'c1', 'd1', '20' ) ;
insert into t values ( 'a1' , 'b1' , 'c1', 'd1', '10' ) ;

commit;

select grp_a, grp_b, count(*)
from   t
group by grp_a, grp_b ;

GRP_A  GRP_B    COUNT(*)
------ ------ ----------
a1     b2              3
a1     b1              2
a2     b3              3

-- (your results may vary)
 

The actual behaviour of GROUP BY without ORDER BY is documented in the SQL Reference Manual as follows.

"The GROUP BY clause groups rows but does not guarantee the order of the result set. To order the groupings, use the ORDER BY clause."

(See AskTom ; Group by behavior in 10GR2 for another discussion of this issue.)