-
group by clause didn't work
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
--------------------------------------
It's not what the world does to you that matters. It's how you respond...
-
Clarification - the order of records is wrong
Just for a clarification, what I mean is that the sort order of records is wrong.
--------------------------------------
It's not what the world does to you that matters. It's how you respond...
-
How do you mean - what was wrong?
-B
-
-
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
--------------------------------------
It's not what the world does to you that matters. It's how you respond...
-
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".
-
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"
-
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
--------------------------------------
It's not what the world does to you that matters. It's how you respond...
-
select owner, object_type, count(*)
from dba_objects
group by owner,object_type
order by owner,object_type
OR
order by 1,2
-
--------------------------------------
It's not what the world does to you that matters. It's how you respond...
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|