Oracle Database - ROWNUM is not working in Oracle:

Asked By vinod kumar on 03-Jan-13 07:46 AM
Hi friends,

       below queries are  not executing, please give me explanation....


DETAILS  Table have 1000000 records....previously ROWNUM is worked but 
ROWNUM is not working now

below query is executing,
SELECT * FROM DETAILS where ROWNUM=1;
but
below queries are  not executing,
SELECT * FROM DETAILS where ROWNUM=2; or    SELECT * FROM DETAILS where ROWNUM>100;  .......etc.





Thanks
Vk
 
sundar k replied to vinod kumar on 04-Jan-13 05:37 PM
When oracle query engine assigns rownum  to a row, Oracle starts at 1 and only only increments the value when a row is selected,  that is, when all conditions in the WHERE clause are met..
since your query is trying to get directly rownum =2 , its not returning any rows, whereas rownum=1 works because the starting number itself is 1. 
sELECT * FROM DETAILS where ROWNUM=2

Queries which has rownum = 1 or rownum <=10 works, whereas queries like rownum=2 or rownum=3 or rownum > 10 will not work.