Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> VIEWS with GROUP BY & ORDER BY - WOW!!!!!!
Few days ago 7.3.4 was replaced with 8.1.5 (NT box)
Look at this:
SQL> select object_type, count(*) num
2 from all_objects
3 where status like 'INVALID'
4 group by object_type
5 ;
OBJECT_TYPE NUM
------------------ --------- PACKAGE 7 PACKAGE BODY 15 PROCEDURE 2
SQL> create view v_invalid_objects as
2 select object_type, count(*) num
3 from all_objects
4 where status like 'INVALID'
5 group by object_type
6 order by num desc;
View created.
SQL> select * from v_invalid_objects;
OBJECT_TYPE NUM
------------------ --------- PACKAGE BODY 15 PACKAGE 7 PROCEDURE 2
Rownum, order by and vice versa in subqueries work fine too...
I'm happy!-)))
Frank Hubeny <fhubeny_at_ntsource.com> wrote in message
news:38364A21.6BA5BD5_at_ntsource.com...
> I tried a similar query on user_tab_columns with a rownum of 5 to conserve
> space:
>
> SQL> SELECT * FROM (
> 2 SELECT COUNT(*), table_name FROM user_tab_columns GROUP BY table_name
order
> by count(*) desc
> 3 )
> 4 WHERE ROWNUM < 5;
>
> COUNT(*) TABLE_NAME
> --------- ------------------------------
> 92 V_192216243_F_5_E_8_8_1
> 89 V_192216243_F_5_E_10_8_1
> 89 V_192216243_F_5_E_9_8_1
> 86 V_192216243_F_5_E_12_8_1
>
> 4 rows selected.
>
> I wonder if the second select could not be seen as a "view" rather than a
> "subquery".
>
Received on Tue Nov 30 1999 - 07:53:30 CST