Why rownum will nor work for greater or equal to checks

Someone asked a question on a forum that why rownum=2 does not work for his query, so i though of sharing some details here.

Using rownum is a tricky affair. Safest bet is to use it only when you want to limit the number of results to be shown. For example rownum<2 or rownum<=5.

Why rownum=2 or rownum>2 will not work?

Read here – http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html

In summary, this is how oracle execute a query

  1. The FROM/WHERE clause goes first.
  2. ROWNUM is assigned and incremented to each output row from the FROM/WHERE clause.
  3. SELECT is applied.
  4. GROUP BY is applied.
  5. HAVING is applied.
  6. ORDER BY is applied.

rownum<=2 clause will get converted to

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

if you change exit when NOT(ROWNUM <= 2) with rownnum=2, you can see it will fail in the first run itself

So if I cannot use rownum, what can I use. Try using row_number() http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions137.htm

It works something like

SELECT last_name FROM
   (SELECT last_name, ROW_NUMBER() OVER (ORDER BY last_name) R FROM employees)
   WHERE R BETWEEN 51 and 100;