Oracle / PLSQL: ROWNUM Function

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:

ROWNUM

Parameters 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.com

Now 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.com

In 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.com

You 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.com

By 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.com

In 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.com

Now we get the backside 2 effects because we have sorted the last_name in descending order.