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
- The FROM/WHERE clause goes first.
- ROWNUM is assigned and incremented to each output row from the FROM/WHERE clause.
- SELECT is applied.
- GROUP BY is applied.
- HAVING is applied.
- 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;