Sunday, September 16, 2012

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. He also wrote the book advertised above:

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>

1 comment:

Laurent Schneider said...

order by in subquery has been introduced in ora8i for the sole purpose of TOP-n queries, select*from(select*from t order by x)where 11>rownum


some functional equivalency to
select top 10
in MS Sql (not tested)
or select limit in mysql