The Magic Of ROWNUM

The “ROWNUM greater than” query never fails to have an eye-popping effect  the first time anyone sees it. If you haven’t worked with ROWNUM in Oracle much before, be prepared!

First things first. What is ROWNUM?

ROWNUM is a pseudocolumn, assigning a number to every row returned by a query. The numbers follow the sequence 1, 2, 3…N, where N is the total number of rows in the selected set. This is useful when you want to do some filtering based on the number of rows, such as:

SQL> -- Rownum to limit result set
SQL> -- to three rows only
SQL> select empno
  2       , ename
  3       , sal
  4       , rownum
  5  from emp
  6  where rownum < 4;

     EMPNO ENAME        SAL     ROWNUM
---------- ---------- ----- ----------
      7369 SMITH        800          1
      7499 ALLEN       1600          2
      7521 WARD        1250          3

So out of those three rows if I want to select only rownum = 2, this should work. Right?

-- Query attempt to select row
-- with rownum = 2
select empno
     , ename
     , sal
     , rownum
from emp
where rownum = 2;

Run it on SQL.

SQL> -- Query attempt to select rows
SQL> -- with rownum = 2
SQL> select empno
  2       , ename
  3       , sal
  4       , rownum
  5  from emp
  6  where rownum = 2;

no rows selected

What just happened?

Why did adding rownum = 2 return no results?

How ROWNUM Works

Here is the secret. Rownum values are not preassigned, they are determined on the fly, as the rows are output. The common misconception is that every row in the table has a permanent ROWNUM. In truth, rows in a table are not ordered or numbered – you cannot ask for row#5 from the table, there is no such thing.

The pseudocode for a query using rownum is:

rownum = 1
for x in ( select * from query)
loop
    if ( x satisfies the predicate )
    then 
          output the row
          rownum = rownum + 1
    end if;
end loop;

The first selected row is always assigned rownum = 1, and is tested against the predicate. When the test is "< 4", rownum = 1 passes the test and the rownum is set to 2, and so the loop continues. The first 3 rows pass the test and get printed out, till rownum becomes 4 and fails the test.

When the test is "= 2", the first row itself does not pass the test (since it is rownum = 1). The increment never happens and no rows get printed.

All of which explains why the WHERE condition can only filter on what rownum is less than, not what it is great than.

Summary

ROWNUM in Oracle is a pseudocolumn that assigns a number to every row returned by a SQL query. It can be of great use in filtering data based on the number of rows returned by the query.

ROWNUM in Oracle gets its value as the query is executed, not before, and gets incremented only after the query passes the WHERE clause. Therefore, your WHERE condition can filter data based on "rownum < 2/3/4/." but not "rownum > 2/3/4.". The second filter will invariably return no rows selected.