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)
