CodesJava

Easy learning with example program codes

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 follow and like us:
error









Copyright © 2019 CodesJava DMCA.com Protection Status