How To Use Order By in Subquery in Oracle

In T-SQL, Order By is supported in subquery. However, in oracle, things are not very straigt foward.

For example, we want to write a sql statement that will delete the most recent entry in Table1.

In T-SQL, we will write:

delete from table1
where key_column = (select top 1 key_column from table1 order by time_column)

This statement will not work in oracle's PL-SQL. You will get an error that looks like following:

ORA-00907: missing right parenthesis

To run the same query in oracle using order by in subquery, you need to put the order by into a view select:

select columnA from (select * from table1 order by columnB) where rownum =1

Of course, without the rownum = 1 limitation, the query above become nonsense. But it is necessary to understand the limitation in oracle.

Finally, our revised query in oracle is like this:

delete from table1
where key_column =
(select key_column from (select * from table1 order by time_column) where rownum = 1)