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:

STUDENT_ID STUDENT_NAME STUDENT_AGE
10 Joy 20
20 Smiley 19
30 Happy 30
40 James 45
50 Bond 18

Query:

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

Output:

ROWNUM STUDENT_ID STUDENT_NAME STUDENT_AGE
1 10 Joy 20
2 20 Smiley 19
3 30 Happy 30
4 40 James 45

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

STUDENT_ID STUDENT_NAME STUDENT_AGE
10 Joy 20
20 Smiley 19
30 Happy 30
40 James 45
50 Bond 18

Query:

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

Output:

ROWNUM STUDENT_ID STUDENT_NAME STUDENT_AGE
3 30 Happy 30
4 40 James 45
1 10 Joy 20
2 20 Smiley 19

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:

STUDENT_ID STUDENT_NAME STUDENT_AGE
10 Joy 20
20 Smiley 19
30 Happy 30
40 James 45
50 Bond 18

Query:

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

Output:

STUDENT_ID STUDENT_NAME STUDENT_AGE
30 Happy 30
40 James 45
10 Joy 20

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.

Please Share