group by clause didn't work

[james_cc]

I recently installed a 9i database, however, I have noticed my group by clause didn't work right by some strange reasons.

SQL> l
1 select owner,object_type,count(*) from
2* dba_objects group by owner,object_type

OWNER OBJECT_TYPE COUNT(*)
------------------------------ ------------------ ----------
RAM SYNONYM 51
SYS LOB 28
SYS TYPE 465
SYS VIEW 2087
SYS INDEX 311
SYS QUEUE 4
SYS TABLE 341
SYS CLUSTER 10
SYS CONTEXT 1
SYS LIBRARY 62
SYS PACKAGE 342

OWNER OBJECT_TYPE COUNT(*)
------------------------------ ------------------ ----------
SYS SYNONYM 6
SYS TRIGGER 10
SYS FUNCTION 41
SYS OPERATOR 1
SYS SEQUENCE 41
SYS JAVA DATA 293
SYS PROCEDURE 25
SYS TYPE BODY 23
SYS JAVA CLASS 8789
SYS JAVA SOURCE 8
SYS PACKAGE BODY 318

OWNER OBJECT_TYPE COUNT(*)
------------------------------ ------------------ ----------
SYS JAVA RESOURCE 173
SYS RESOURCE PLAN 3
SYS CONSUMER GROUP 4
SYS EVALUATION CONTEXT 1
HIEN TABLE 4
HIEN SYNONYM 51

[james_cc]

Just for a clarification, what I mean is that the sort order of records is wrong.

[bmycroft]

How do you mean - what was wrong?

-B

[fraze]

Whats wrong with that?

[james_cc]

HIEN should come before RAM and SYS. Does GROUP BY also order the records for you?
It works only on the 8.17 environment I have listed below.

8.17 environment
-----------------------

SQL> select owner,object_type,count(*) from
2 dba_objects group by owner,object_type;

OWNER OBJECT_TYPE COUNT(*)
------------------------------ ------------------ ----------
ALUMNI FUNCTION 15
ALUMNI INDEX 380
ALUMNI PROCEDURE 68
ALUMNI TABLE 320
ALUMNI TRIGGER 4
BANINST1 FUNCTION 190
BANINST1 PACKAGE 199
BANINST1 PACKAGE BODY 198
BANINST1 PROCEDURE 21
BANINST1 VIEW 138
BANSECR INDEX 7

OWNER OBJECT_TYPE COUNT(*)
------------------------------ ------------------ ----------
BANSECR PACKAGE 3
BANSECR PACKAGE BODY 3
BANSECR SEQUENCE 1
BANSECR TABLE 8
BANSECR VIEW 2
DBSNMP SYNONYM 4
GENERAL INDEX 209
GENERAL SEQUENCE 6
GENERAL TABLE 199
GENERAL TRIGGER 23
GENERAL VIEW 2

OWNER OBJECT_TYPE COUNT(*)
------------------------------ ------------------ ----------
HIEN SYNONYM 51
HIEN TABLE 4
JAMES TABLE 1
MBOOTE INDEX 1
MBOOTE TABLE

[DaPi]

You should not expect them to be sorted. Under 7.3.4 you got the sort as a free bonus - from 8.something you have to include the "order by".

[DaPi]

Originally posted by james_cc
It works only on the 8.17 environment I have listed below.

That is luck. In general in 8.1.7 you need "order by"

[james_cc]

Just curious, how can I easily combine group by and order by in order to get the count(*) and sort order to work at the same time? Since I have never used the two together that would work, unless I rewrite the query using field alias etc.


1 select owner,object_type,count(*) from
2* dba_objects group by owner,object_type

[DaPi]

select owner, object_type, count(*)
from dba_objects
group by owner,object_type
order by owner,object_type

OR

order by 1,2

[james_cc]

Thanks Dapi