EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign up
Home Software Development Software Development Tutorials PL/SQL Tutorial PL/SQL ROWNUM

PL/SQL ROWNUM

Updated April 5, 2023

PL/SQL ROWNUM

Introduction to PL/SQL ROWNUM

PL/SQL provides different types of functions to the user, in which ROWNUM() is one of the functions provided by the PLSQL. Basically, ROWNUM() function is used to return the number in which that oracle selects the rows from the specified table or joined table that is dependent on the user requirement. After the execution of the ROWNUM() function, it returns the rows in a sequential manner, such as 1, 2, and so on. We can perform the different operations by using the ROWNUM() function that we can use with an order by clause and Limit clause.

Start Your Free Software Development Course

Web development, programming languages, Software testing & others

Syntax:

select ROWNUM, colm name from specified table name <specified expression order by column name;

Explanation

In the above syntax, we used the ROWNUM() function with different parameters as follows.

First, we use a select clause with ROWNUM() Function as shown in the above syntax.

  • colm name: Colm name means actual column name from the specified table name.
  • specified table name: Specified table means actual table names that are created by the user.
  • <specified expression: This parameter is optional in this syntax; if required, then we can specify the expression.
  • order by column name: At the end of syntax, we used to order by column name; this is also an optional part of this syntax.

How ROWNUM works in PL/SQL?

Now let’s see how the ROWNUM() function works in PL/SQL as follows. In a query, ROWNUM is a pseudo column (not an actual column) that is accessible. The numbers 1, 2, 3, 4,…. will be allocated to ROWNUM. ROWNUM is used with a set of N rows, where N is the number of rows in the set. Basically, the ROWNUM() function does not assign the permanent values to the rows, and during the execution, it does not ask any number to display the result.
Many individuals are also perplexed as to when a ROWNUM value is really assigned. After a row passes the predicate step of the query, but before any sorting or aggregation, a ROWNUM value is issued to it. Furthermore, a ROWNUM number is only incremented after it is allocated, so then why does the following query not return a row as follows.

select * from specified table name where ROWNUM >1;

Explanation

In the above statement, we write the ROWNUM is greater than 1, and this condition is not true for the first row. The ROWNUM() function does not directly show the second row from the table. As a result, no ROWNUM value may ever be larger than 1. Consider the following query structure.

Select…., ROWNUM
From specified table name
Where <Condition>
Group by<Specified Column Name>
Having <condition for having clause>
Order by <specified column name>

The above statement will be executed in the following order as follows.

1. It first executes the either from or where clause.
2. After that ROWNUM() function assigned the number in increment order.
3. Now applied the select clause.
4. After selection, the clause group by clause is applied.
5. After group by clause, a having clause is applied.
6. Finally, Order by clause is applied.

If ROWNUM is followed by an ORDER BY clause in the same query, the rows will be reordered by the ORDER BY clause. The outcomes may differ based on how the rows are accessed. For example, if Oracle uses an index to access the data because of the ORDER BY clause, Oracle may get the rows in a different order than if the index wasn’t used. As a result, the following sentence will have a different result from the prior example.

Select * from specified table name where ROWNUM < expression order by Column name;

If you embed the ORDER BY clause in a subquery, you may force the ROWNUM condition to be applied after the ORDER BY clause in the subquery and the ROWNUM condition in the top-level query.

Examples

Now let’s see different examples of the ROWNUM() function in PL/SQL for better understanding as follows.

First, we need to create the table by using the following create table statement as follows.

create table students(stud_id number(10) not null, stud_name varchar2(30) not null, stud_city varchar2(30));

Explanation

In the above example, we use the create table statement to create a new table name as students with different attributes such as stud_id, stud_name, and stud_city with different data types, as shown in the above statement. The final output of the above statement we illustrated by using the following screenshot as follows.

pl sql 1

Now insert some records to perform the ROWNUM() function as follows.

insert into students(stud_id, stud_name, stud_city) values(101,'Jenny','Mumbai');
insert into students(stud_id, stud_name, stud_city) values(102,'Johan','Mumbai');
insert into students(stud_id, stud_name, stud_city) values(103,'Pooja','London');
insert into students(stud_id, stud_name, stud_city) values(104,'Sameer','London');
insert into students(stud_id, stud_name, stud_city) values(105,'Rohit', 'London');

Explanation

By using insert into the statement, we inserted 5 different records as shown in the above statement. The final output of the above statement we illustrated by using the following screenshot as follows.

SQL 2

Now everything is ready to perform the ROWNUM() function as follows.

select ROWNUM, students.* from students where stud_id>101;

Explanation

In the above example, we try to implement the ROWNUM() function; here, first, we use the select clause with ROWNUM() to get the number in a sequential manner from the student’s table. After that, we specify the condition by using the where clause; in this example, we need to display rows from whose stud_id is greater than 101, as shown. The final output of the above statement we illustrated by using the following screenshot as follows.

pl sql 3

Now let’s see how we can use order by clause with ROWNUM() function as follows.

select ROWNUM, students.* from students where stud_id>101 order by stud_name;

Explanation

The final output of the above statement we illustrated by using the following screenshot as follows.

pl sql 4

select * from(select students.* from students where stud_id>101 order by st ud_name) where ROWNUM<3;

Explanation

In the above example, we try to implement a sub-query with the ROWNUM() function. The final output of the above statement we illustrated by using the following screenshot as follows.

pl sql 5

Now let’s see how we can use subquery with an order by clause, either ASC or DESC order.

select * from(select students.* from students where stud_id>101 order by st ud_name desc) where ROWNUM<3;

Explanation

The final output of the above statement we illustrated by using the following screenshot as follows.

output 1

Conclusion

We hope from this article you learn PL/SQL ROWNUM. From the above article, we have learned the basic syntax of ROWNUM, and we also see different examples of the ROWNUM. From this article, we learned how and when we use PL/SQL ROWNUM.

Recommended Articles

We hope that this EDUCBA information on “PL/SQL ROWNUM” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. Careers in PL/SQL
  2. PL/SQL TRIM
  3. PL/SQL Date Functions
  4. PL/SQL Collections
ADVERTISEMENT
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
Financial Analyst Masters Training Program
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Data Science Bundle
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Software Development Bundle
5000+ Hours of HD Videos
149 Learning Paths
1050+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
Footer
Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Blog as Guest
Courses
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Software Development Course

Web development, programming languages, Software testing & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Extended Cyber Monday Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW