Oracle Database..

I learnt

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:  1 2 3 4 5 6

SQL> -- Rownum to limit result set

SQL> -- to three rows only

SQL> select empno, ename, sal, rownum

 from emp

 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.

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 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 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.