Tuesday, November 9, 2010

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)


3 comments :

  1. Leo Huang, thank you! This post saved my day!

    ReplyDelete
  2. wow great Article, the details you have provided are much clear, easy to understand, if you post some more Article, it will be very much useful for me.
    PL/SQL Training in Chennai

    ReplyDelete