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:
SELECT column(s)
FROM table_name
WHERE ROWNUM < n;
Example 1:
Suppose we have a table named ‘employee’ as shown below:
| Employee_Id | Employee_Name | Salary | Department | Commission |
| 101 | Emp A | 10000 | Sales | 10 |
| 102 | Emp B | 20000 | IT | 20 |
| 103 | Emp C | 28000 | IT | 20 |
| 104 | Emp D | 30000 | Support | |
| 105 | Emp E | 32000 | Sales | 10 |
If we write out query as:
SELECT * FROM employee WHERE ROWNUM <3;
We will get the following result:
| Employee_Id | Employee_Name | Salary | Department | Commission |
| 101 | Emp A | 10000 | Sales | 10 |
| 102 | Emp B | 20000 | IT | 20 |
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
Or
WHERE ROWNUM BETWEEN n AND m
Or
WHERE ROWNUM IN(a,b,c…n)