Oracle / PLSQL: ROWNUM Function

This Oracle tutorial explains how to use the Oracle/PLSQL ROWNUM function with syntax and examples.

Description

The Oracle/PLSQL ROWNUM function returns a number that represents the order that a row is selected by Oracle from a table or joined tables. The first row has a ROWNUM of 1, the second has a ROWNUM of 2, and so on.

Syntax

The syntax for the ROWNUM function in Oracle/PLSQL is:

ROWNUM

Parameters or Arguments

There are no parameters or arguments for the ROWNUM function.

Note

• The ROWNUM function is sometimes referred to as a pseudocolumn in Oracle.

Returns

The ROWNUM function returns a numeric value.

Applies To

The ROWNUM function 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 want to follow along with this tutorial, get the DDL to create the tables and the DML to populate the data. Then try the examples in your own database!

Example

Let's look at some Oracle ROWNUM function examples and explore how to use the ROWNUM function in Oracle/PLSQL.

In this ROWNUM example, we have a table called customers with the following data:

CUSTOMER_ID  LAST_NAME  FIRST_NAME  FAVORITE_WEBSITE
-----------  ---------  ----------  ---------------------

       4000  Jackson    Joe         www.aodba.com
       5000  Smith      Jane        www.cook.com
       6000  Jack   Samantha    www.motor.com
       7000  Reynolds   Allen       www.mysite.com
       8000  James   Paige
       9000  Johnson    Derek       www.aodba.com

Now let's demonstrate how the ROWNUM function works by selecting data from the customers table. Enter the following SQL statement 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.cook.com
     2         6000  Jack   Samantha    www.motor.com
     3         7000  Reynolds   Allen       www.mysite.com
     4         8000  James   Paige
     5         9000  Johnson    Derek       www.aodba.com

In this example, the ROWNUM function returns 1 for the first record, 2 for the second record, and so on. Since this is a very simple example, it would appear that the ROWNUM function is straight-forward to use, but it is a bit more complicated than you think.

Let's complicate the example by introducing an ORDER BY clause and sort the results by last_name in ascending order. Enter the following SELECT statement 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   James    Paige
     2          6000   Jack    Samantha     www.motor.com
     5          9000   Johnson     Derek        www.aodba.com
     3          7000   Reynolds    Allen        www.mysite.com
     1          5000   Smith       Jane         www.cook.com

You would expect that the first row in your result set would have a ROWNUM value of 1, but in this example, it has a ROWNUM value of 4. Why is this? Well, it depends how Oracle accessed the rows in the query. For example your results can vary depending on a lot of factors (ie: the order that you inserted the data in the table or if there is an index on the table).

Because of these factors, there is a right and wrong 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 sort your results and then place the ROWNUM function 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  James   Paige
     2         6000  Jack   Samantha    www.motor.com
     3         9000  Johnson    Derek       www.aodba.com
     4         7000  Reynolds   Allen       www.mysite.com
     5         5000  Smith      Jane        www.cook.com

By using a subquery in this way, it forces the ROWNUM to properly 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 also handy if you want to limit the results of a query. For example, you could return the top 2 results. Enter the following SQL statement 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  James   Paige
       6000  Jack   Samantha    www.motor.com

In this example, the ROWNUM function would return the top 2 results because we want ROWNUM < 3.

If we wanted to get the bottom 2 results, we could just change the sort 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.cook.com
       7000  Reynolds   Allen       www.mysite.com

Now we get the bottom 2 results because we have sorted the last_name in descending order.