How rownum works in oracle

ROWNUM in oracle. It’s a pseudo column. It always bewildered me how it fetches the values.

But it started haunting me. So I googled it and found the answer.

The ROWNUM will be initialized to one before execution of query.

Oracle always increment the rownum value after fetching every row.

So the select * from employees where rownum <=2 works whereas the >=2 or >2 or =2 won’t work.

The explanation I found from http://www.oracle.com/

Code snippet from http://www.oracle.com/:

select * from emp where ROWNUM <= 5 order by sal desc;

The above query will execute like
ROWNUM = 1

for x in ( select * from emp )
loop
exit when NOT(ROWNUM <= 5)
OUTPUT record to temp
ROWNUM = ROWNUM+1
end loop
SORT TEMP


It’s a pretty good explanation about rownum
http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html