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)


5 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
  3. Of the country’s 17 casinos, 16 are for foreigners solely, though Koreans who can produce an overseas passport can 카지노사이트 even play. As the nation’s solely casino that welcomes all Koreans as well as|in addition to} foreigners, Kangwon Land revenue–US$1.2 billion final year–exceeds that of Korea’s other casinos mixed. Projected to open in 2023,Inspire is actively underneath building however is already dwelling a lot as} its name with a vision in contrast to|not like} any round. Visitors may even be able to|be capable of|have the ability to} expertise refined cultural content material from prime global creators of varied forms, corresponding to art, exhibitions and performances. Twentieth Century Fox World Korea - Another proposed built-in resort in Korea that can probably never see the sunshine of day.

    ReplyDelete