Subquery with Order By?

This was tested on Oracle 11.1.0.6.0 running on Windows XP. Looking through some course notes from 1990 (as you do), I read that you cannot include an order by in a subquery. I'm not sure why you would ever want to do such a thing but I decided to see what happened if you did:

SQL> l
  1  select count(*)
  2  from dba_tables
  3  where table_name not in
  4  (select table_name
  5   from dba_indexes
  6*  order by 1)
SQL> /
 order by 1)
 *
ERROR at line 6:
ORA-00907: missing right parenthesis

SQL> l
  1  select count(*)
  2  from dba_tables
  3  where table_name not in
  4  (select table_name
  5   from dba_indexes
  6*  order by table_name)
SQL> /
 order by table_name)
 *
ERROR at line 6:
ORA-00907: missing right parenthesis

SQL>

It gave me an ORA-00907, as you can see above, which was not especially helpful. I removed the order by and the query ran successfully:

SQL> l
  1  select count(*)
  2  from dba_tables
  3  where table_name not in
  4  (select table_name
  5*  from dba_indexes)
SQL> /

  COUNT(*)
----------
       444

SQL>

However, I have just thought of a special kind of subquery, sometimes called an in-line view, which is allowed to have an order by. Here is an example, suggested by Laurent in the 1st comment below:

SQL> l

  1  select * from

  2  (select sid, a.value/100 CPU_Seconds

  3   from v$sesstat a, v$sysstat b

  4   where a.statistic# = b.statistic#

  5   and name = 'CPU used by this session'

  6   order by a.value desc)

  7* where rownum < 6

SQL> /

       SID CPU_SECONDS

---------- -----------

        71      401.64

       140      361.04

        10      306.59

         9         306

        43      280.75

SQL>