Why ROWNUM =(> 1) will not work ?

Oracle Apps Interview QuestionsCategory: SQLWhy ROWNUM =(> 1) will not work ?
Questions Master asked 10 years ago

SELECT * FROM emp
WHERE rownum = 5;

Why above query wont retrieve records?

1 Answers
Shailender Thallam Staff answered 10 years ago

Firstly, ROWNUM is not a column, its a pseudo column which gets value only in the run time. The value of ROWNUM gets incremented by 1 only after fetching the row data. lets take a scenario ROWNUM=2. When oracle fetches the first row it assigns the ROWNUM as 1 and then checks if the condition ROWNUM=2 is satisfied or not, as the condition is not satisfied the oracle engine skips the first row and now 2nd row would become the 1st row of our output and it gets assigned with ROWNUM as 1 (ROWNUM is not incremented as our first row is skipped) and again our condition ROWNUM=2 is false and this check goes for all the records of the output. The ROWNUM never gets incremented and no records will be fetched.
Thats why ROWNUM > N doesn’t work and ROWNUM = 1 or ROWNUM < N works fine.

SELECT * from emp WHERE rownum < 6;

SELECT * FROM emp WHERE rownum = 1;
The above two queries will work fine.