ROWNUM in Oracle

ROWNUM is one of the vital Numeric/Math functions of Oracle. It is used to get a number that represents the order in which a row from a table or joined tables is selected by the Oracle. The ROWNUM function is supported in the various versions of the Oracle/PLSQL, including, Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i and Oracle 8i.

Syntax:

ROWNUM 

Example 1:
Students Table:

Query:

SELECT ROWNUM, students.*
FROM students
WHERE student_age > 18;

Output:

Explanation:
Here, the ROWNUM function returns 1 for the first row, 2 for the second row, and so on.
Example 2:
Students Table:

Query:

SELECT ROWNUM, students.*
FROM students
WHERE student_age > 18;
ORDER BY student_name;

Output:

Explanation:
Here, the ROWNUM function returns the number for the rows but not in a sequence. The reason behind this is the way Oracle accessed the query. This can be because of the index for the rows or in the order the records were added to the table.

Example 3:
Students Table:

Query:

SELECT *
FROM (
SELECT students.*
FROM students
WHERE student_age > 18;
ORDER BY student_name )
WHERE ROWNUM < 4;

Output:

Explanation:
Here, we are using the ROWNUM function to limit the results. The ROWNUM function is returning only the top 3 results because we want ROWNUM < 4.