This article is written about how to use the Oracle/PLSQL ROWNUM characteristic with syntax and examples.
Description
The Oracle/PLSQL ROWNUM function returns a variety that represents the order that a row is chosen by using Oracle from a desk or joined tables. The first row has a ROWNUM of 1, the 2nd has a ROWNUM of 2, and so on.
Syntax
The syntax for the ROWNUM feature in Oracle/PLSQL is:
ROWNUMParameters or Arguments
There are no parameters or arguments for the ROWNUM function.
Note
The ROWNUM characteristic is once in a while referred to as a pseudocolumn in Oracle.
Returns
The ROWNUM function returns a numeric value.
Applies To
The ROWNUM feature can be used in the following versions of Oracle/PLSQL:
Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i
DDL/DML for Examples
If you prefer to observe alongside with this tutorial, get the DDL to create the tables and the DML to populate the data. Then attempt the examples in your own database!
Get DDL/DML
Example
Let’s seem to be at some Oracle ROWNUM feature examples and explore how to use the ROWNUM characteristic in Oracle/PLSQL.
In this ROWNUM example, we have a table referred to as customers with the following data:
CUSTOMER_ID LAST_NAME FIRST_NAME FAVORITE_WEBSITE
----------- --------- ---------- ---------------------
4000 Jackson Joe www.techonthenet.com
5000 Smith Jane www.digminecraft.com
6000 Ferguson Samantha www.bigactivities.com
7000 Reynolds Allen www.checkyourmath.com
8000 Anderson Paige
9000 Johnson Derek www.techonthenet.comNow let’s show how the ROWNUM function works by means of choosing data from the customers table. Enter the following SQL announcement in Oracle:
SELECT ROWNUM, customers.*
FROM customers
WHERE customer_id > 4500;These are the results that you should see:
ROWNUM CUSTOMER_ID LAST_NAME FIRST_NAME FAVORITE_WEBSITE
------ ----------- --------- ---------- ---------------------
1 5000 Smith Jane www.digminecraft.com
2 6000 Ferguson Samantha www.bigactivities.com
3 7000 Reynolds Allen www.checkyourmath.com
4 8000 Anderson Paige
5 9000 Johnson Derek www.techonthenet.comIn this example, the ROWNUM characteristic returns 1 for the first record, two for the 2d record, and so on. Since this is a very easy example, it would show up that the ROWNUM characteristic is straight-forward to use, however it is a bit extra complicated than you think.
Let’s complicate the example by using introducing an ORDER BY clause and sort the consequences by using last_name in ascending order. Enter the following SELECT declaration in Oracle:
SELECT ROWNUM, customers.*
FROM customers
WHERE customer_id > 4500
ORDER BY last_name;You should see these results:
ROWNUM CUSTOMER_ID LAST_NAME FIRST_NAME FAVORITE_WEBSITE
------ ----------- --------- ---------- ---------------------
4 8000 Anderson Paige
2 6000 Ferguson Samantha www.bigactivities.com
5 9000 Johnson Derek www.techonthenet.com
3 7000 Reynolds Allen www.checkyourmath.com
1 5000 Smith Jane www.digminecraft.comYou would count on that the first row in your result set would have a ROWNUM value of 1, however in this example, it has a ROWNUM cost of four Why is this? Well, it depends how Oracle accessed the rows in the query. For example your consequences can differ depending on a lot of elements (ie: the order that you inserted the facts in the desk or if there is an index on the table).
Because of these factors, there is a proper and incorrect way to use the ROWNUM function.
Recommended Way to Use ROWNUM
The most reliable way to use the ROWNUM is to use a subquery to filter and type your outcomes and then region the ROWNUM feature in the outer SELECT. Enter the following SELECT in Oracle:
SELECT ROWNUM, a.*
FROM (SELECT customers.*
FROM customers
WHERE customer_id > 4500
ORDER BY last_name) a;These are the results that you should see:
ROWNUM CUSTOMER_ID LAST_NAME FIRST_NAME FAVORITE_WEBSITE
------ ----------- --------- ---------- ---------------------
1 8000 Anderson Paige
2 6000 Ferguson Samantha www.bigactivities.com
3 9000 Johnson Derek www.techonthenet.com
4 7000 Reynolds Allen www.checkyourmath.com
5 5000 Smith Jane www.digminecraft.comBy using a subquery in this way, it forces the ROWNUM to suitable order the records, starting at 1 for the first record, 2 for the second and so on.
Use ROWNUM to Limit Results
The ROWNUM function is additionally handy if you prefer to restriction the effects of a query. For example, you ought to return the top 2 results. Enter the following SQL assertion in Oracle:
SELECT *
FROM (SELECT customers.*
FROM customers
WHERE customer_id > 4500
ORDER BY last_name)
WHERE ROWNUM < 3;You should see these results:
CUSTOMER_ID LAST_NAME FIRST_NAME FAVORITE_WEBSITE
----------- --------- ---------- ---------------------
8000 Anderson Paige
6000 Ferguson Samantha www.bigactivities.comIn this example, the ROWNUM characteristic would return the top two consequences due to the fact we choose ROWNUM < three
If we desired to get the backside 2 results, we could simply change the kind order of the subquery to last_name DESC. Enter the following query in Oracle:
SELECT *
FROM (SELECT customers.*
FROM customers
WHERE customer_id > 4500
ORDER BY last_name DESC)
WHERE ROWNUM < 3;These are the results that you should see:
CUSTOMER_ID LAST_NAME FIRST_NAME FAVORITE_WEBSITE
----------- --------- ---------- -------------------
5000 Smith Jane www.digminecraft.com
7000 Reynolds Allen www.checkyourmath.comNow we get the backside 2 effects because we have sorted the last_name in descending order.