RE: group by sorting

From: larry elkins <elkinsl_at_flash.net>
Date: Wed, 18 Oct 2000 07:51:03 -0500
Message-Id: <10653.119629@fatcity.com>

Chris,

Others have already pointed out the use of an ORDER BY DESC.

On a related note, in your original question, you mentioned a GROUP BY defaulting to an ascending sort. It's worth pointing out that one cannot depend on the GROUP BY to always sort the data the way one expects. Even if wanting an ascending sort, you should still include an ORDER BY clause. I'm not trying to be picky here -- I just want to make sure that you, or others, don't get bit by this assumption somewhere down the road. I've seen it happen a few times.

Tom Kyte, an Oracle employee considered very competent by many, posted something on usenet quite some time ago concerning this. Following are portions of what he said, along with examples that I executed, based on his examples, illustrating his point.

<<<Start Paraphrasing Kyte>>>

Kyte: It has always been true that group by does not have to sort (and in different character sets you can get truly strange results as group by does a binary sort when it does sort -- not a character set sort).

Example 1:

SQL> create table empx as select * from emp where rownum <= 5;

Table created.

SQL> create index emp_idx on empx (job,ename);

Index created.

SQL> select ename, job
  2 from empx
  3 where ename >chr(0)
  4 and job >chr(0)
  5 group by ename, job
  6 /

ENAME JOB
---------- ---------

SMITH      CLERK
JONES      MANAGER
ALLEN      SALESMAN
MARTIN     SALESMAN
WARD       SALESMAN

Kyte: 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.

Kyte: 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!

Example 2:

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_1   2 from the_table
  3 group by nk1, nk2, pk1, pk2;

      NK1 NK2 PK1 PK2 A_COUNT_1 --------- --------- --------- --------- ---------

        2         2         1         1         1
        1         1         2         1         1

<<<End Paraphrasing Kyte>>>

The bottom line here is that you still need to use an ORDER BY with your GROUP BY to ensure the results are properly ordered.

Regards,

Larry Elkins

-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Bowes, Chris Sent: Tuesday, October 17, 2000 5:23 PM
To: Multiple recipients of list ORACLE-L Subject: group by sorting

I have looked in a couple manuals for this but have not found it. When doing a group by, a sort is done on the data. Is there anyway to specify a descending order for that sort? It is defaulted to ascending. Is there a way of overriding that? I told the developer that she should put it into a subquery but the query itself (all 3 pages) is already complex enough. Any Received on Wed Oct 18 2000 - 07:51:03 CDT