Skip to Main Content
  • Questions

  • In Select Part of a query, ORDER BY follow by ROWNUM is not allowed, raising ORA-00907

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Giang Doan.

Asked: December 13, 2022 - 12:59 pm UTC

Last updated: December 16, 2022 - 10:45 am UTC

Version: 21c

Viewed 1000+ times

You Asked

Hi AskTom Team,

"Select dummy, 'Just a test rownum and order in SELECT part' acomment, (Select dummy from dual where rownum= 1 order by dummy) ask_case from dual;"

Please explain me why the query above cannot be executed when I add order by dummy after limiting the number of records using rownum.
The child_query runs alone succesfully but when it is associated with father_query, raising a ORA-00907 error.

I can get round this error easily but I want to know how rownum and orderby work in SQL machine.

Many thanks for your help.

Giang Doan



with LiveSQL Test Case:
https://livesql.oracle.com/apex/livesql/s/og6fbhpcvszcypl0uf1spbjg4

and Connor said...

The issue there is that you are using it in a scalar query, ie, a select as a column.

Easier if we format it:
SQL> select
  2    dummy,
  3    'Just a test rownum and order in SELECT part' acomment,
  4    (Select dummy
  5     from dual
  6     where rownum= 1
  7     order by dummy) ask_case
  8  from dual;
   order by dummy) ask_case
   *
ERROR at line 7:
ORA-00907: missing right parenthesis


Its unrelated to rownum, for example:

SQL> select
  2    dummy,
  3    'Just a test rownum and order in SELECT part' acomment,
  4    (Select dummy
  5     from dual
  6     order by dummy) ask_case
  7  from dual;
   order by dummy) ask_case
   *
ERROR at line 6:
ORA-00907: missing right parenthesis


In all cases (scalar or not) the query:

select ..
from  any_table
where rownum <= ...
order by ....


is almost always a bug because of the way SQL works, namely:

- apply the WHERE predicate first
- THEN apply the ORDER BY

Let's say we have "rownum <= 10". This means we just grab 10 *random* rows, and then sort them. It does NOT get the 10 highest/lowest rows.

For that you need an inline view or the FETCH FIRST syntax.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks for your quick response!

Giang Doan, December 14, 2022 - 8:28 pm UTC

Absolute correct.
Many thanks.

Giang Doan
Chris Saxon
December 15, 2022 - 4:49 pm UTC

You're welcome

subsubquery

A reader, December 16, 2022 - 9:18 am UTC 

select (select dummy from dual order by dummy) x
from dual;

ORA-00907: missing right parenthesis

select (select * from (select dummy from dual order by dummy)) x
from dual;

X
-
X

select (select dummy from dual order by dummy fetch first 100 percent rows only) x
from dual;

X
-
X

Row limiting clause is transformed to a subquery along with order by and can be used in an outer scalar subquery.
Chris Saxon
December 16, 2022 - 10:45 am UTC

Nice investigating
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.