Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Oracle SELECT query with rownum option

 
jaya kemmannu
Ranch Hand
Posts: 89
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 89
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Thanks Martin !!!
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You're welcome!
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic