ROWNUM Pseudo Column in Oracle SQL – PLSQL

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)


Tagged , , , , , , , , , , , . Bookmark the permalink.