The ROWNUM is a pseudo column in Oracle SQL / PLSQL which returns a row’s position in the fetched result set.
ROWNUM is evaluated AFTER records are SELECTED from the data-base and BEFORE execution of the ORDER BY clause.
Syntax for the ROWNUM function in Oracle SQL / PLSQL is:
WHERE ROWNUM < n;
Suppose we have a table named ‘employee’ as shown below:
If we write out query as:
SELECT * FROM employee WHERE ROWNUM <3;
We will get the following result:
Here we can see that the PSEUDO COLUMN ROWNUM has restricted the number of rows in the fetched result to 2.
However ROWNUM always returns no data when used as:
WHERE ROWNUM > n
WHERE ROWNUM BETWEEN n AND m
WHERE ROWNUM IN(a,b,c…n)