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