aspose file tools*
The moose likes JDBC and the fly likes Oracle SELECT query with rownum option Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Oracle SELECT query with rownum option" Watch "Oracle SELECT query with rownum option" New topic
Author

Oracle SELECT query with rownum option

jaya kemmannu
Ranch Hand

Joined: Sep 23, 2011
Posts: 85

Hi,

I have two oracle select query with rownum as :

SQL> select np_txn_id from testdb where rownum<2;

NP_TXN_ID
----------
19649223

SQL> select min(np_txn_id) from testdb where rownum<2;

MIN(NP_TXN_ID)
--------------
35

Can anybody explain why first query did not return oldest record ?

Regards,
Joy
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Database rows in general do not have a defined order. Unless you use order by, the database is free to return rows in any order it wants. Sometimes this order corresponds to the order in which rows are physically stored in the table, but it is never guaranteed. See also our FAQ page on Order by.

Specifically for Oracle's rownum, just adding the ORDER BY is not enough. Rownums are assigned and the where condition is evaluated before the rows are sorted. This is somewhat counter-intuitive, but that's life . So, in Oracle, you always need to use nested queries with rownum. In your case, it would be:
jaya kemmannu
Ranch Hand

Joined: Sep 23, 2011
Posts: 85

Thanks Martin !!!
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

You're welcome!
 
wood burning stoves
 
subject: Oracle SELECT query with rownum option