GROUP BY != ORDER BY

(The following example sourced from a newsgroup post by T. Kyte)

Here are 2 simple examples of group by not sorting:

SQL> create table emp as select * from scott.emp where rownum <= 5;
Table created.

SQL> create index emp_idx on emp(job,ename);
Index created.

SQL> select ename, job from emp where ename >chr(0) and job >chr(0)
  2  group by ename, job ;

ENAME      JOB
---------- ---------
smith      CLERK
jones      MANAGER
allen      SALESMAN
martin     SALESMAN
ward       SALESMAN

group by ename,job != order by ename, job. Here the optimizer used an index to get the answer and realizing the Group by ename, job was the same as group by job, ename -- did it that way. think about what the effect on your application would be if the emp_idx was added AFTER your application went production.

Here is another small example that doesn't even need the where clause on a table. All that happened was someone analyzed the table. All of a sudden, the group by that sorted -- stops sorting!

SQL> create table the_table ( pk1 int,
  2                           pk2 int,
  3                           nk1 int,
  4                           nk2 int,
  5                           primary key(pk1,pk2) )
  6  /
Table created.

SQL> create index the_table_idx on the_table(pk1,nk1,pk2,nk2);
Index created.

SQL> insert into the_table values (1, 1, 2, 2 );
1 row created.

SQL> insert into the_table values (2, 1, 1, 1 );
1 row created.

SQL> select nk1, nk2, pk1, pk2, count(*) a_count_1
  2    from     the_table
  3    group by nk1, nk2, pk1, pk2;

       NK1        NK2        PK1        PK2  A_COUNT_1
---------- ---------- ---------- ---------- ----------
         1          1          2          1          1
         2          2          1          1          1

SQL> analyze table the_table compute statistics;
Table analyzed.

SQL> select nk1, nk2, pk1, pk2, count(*) a_count_2
  2    from     the_table
  3    group by nk1, nk2, pk1, pk2;

       NK1        NK2        PK1        PK2  A_COUNT_2
---------- ---------- ---------- ---------- ----------
         2          2          1          1          1
         1          1          2          1          1