What’s The Difference Between Oracle ROWNUM vs Oracle ROW_NUMBER?

Have you seen ROWNUM and ROW_NUMBER in Oracle and wondered what the difference is? Learn what they are and the differences between Oracle ROWNUM vs Oracle ROW_NUMBER in this article.

What Is Oracle ROWNUM?

Oracle ROWNUM is a pseudocolumn that assigns a number to each row returned by a query. It’s assigned before an ORDER BY is performed, so you shouldn’t order by the ROWNUM value.

You might think that ROWNUM is a function in Oracle.

However, it’s not a function. It’s a “pseudocolumn”. It acts like a column but it’s not defined on the table.

ROWNUM assigns a number to each row returned by a query. It is assigned before an ORDER BY is performed.

For now, let’s see an example of the Oracle ROWNUM pseudocolumn.

SELECT ROWNUM, first_name, last_name, address_state
FROM student;

Result:

ROWNUM	FIRST_NAME	LAST_NAME	ADDRESS_STATE
1	John	Smith	New York
2	Susan	Johnson	Colorado
3	Tom	Capper	Nevada
4	Mark	Holloway	New York
5	Steven	Webber	New York
6	Julie	Armstrong	Texas
7	Michelle	Randall	Florida
8	Andrew	Cooper	Texas
9	Robert	Pickering	Colorado
10	Tanya	Hall	Texas
11	Jarrad	Winston	Utah
12	Mary	Taylor	Oregon
13	Mark	Anderson	California
14	John	Rogers	Nevada

As you can see, the ROWNUM value has been added as the first column of the result set.

What Is Oracle ROW_NUMBER?

Oracle ROW_NUMBER is an Oracle analytic function. It assigns a number to each row, depending on the parameters to the function, which define the ordering within a group of rows.

Many window functions follow a similar syntax, and ROW_NUMBER is one of those.

Let’s see an example of the Oracle ROW_NUMBER function.

SELECT first_name, last_name, address_state,
ROW_NUMBER() OVER (PARTITION BY address_state ORDER BY last_name) AS row_number_value
FROM student;

Result:

FIRST_NAME	LAST_NAME	ADDRESS_STATE	ROW_NUMBER_VALUE
Mark	Anderson	California	1
Susan	Johnson	Colorado	1
Robert	Pickering	Colorado	2
Michelle	Randall	Florida	1
Tom	Capper	Nevada	1
John	Rogers	Nevada	2
Mark	Holloway	New York	1
John	Smith	New York	2
Steven	Webber	New York	3
Mary	Taylor	Oregon	1
Julie	Armstrong	Texas	1
Andrew	Cooper	Texas	2
Tanya	Hall	Texas	3
Jarrad	Winston	Utah	1

As you can see, it shows all of the records in the student table. It has ordered the records by address_state, and then by last_name.

For each record that has the same address_state value, a different number has been assigned. This is based on the records being ordered by the last_name value, then assigned a sequential number.

Susan Johnson has a value of 1, and Robert Pickering has a value of 2, because they are both in Colorado but Johnson comes before Pickering. Mark Anderson also has a value of 1, but the address_state is different, so this is OK.

SQL Server also has a ROW_NUMBER function, which I’ve written about here.

 

What Are the Differences Between Oracle ROWNUM vs ROW_NUMBER?

There are a few differences between ROWNUM and ROW_NUMBER:

• ROWNUM is a pseudocolumn and has no parameters. ROW_NUMBER is an analytical function which takes parameters.
• ROWNUM is calculated on all results but before the ORDER BY. ROW_NUMBER is calculated as part of the column calculation.
• ROWNUM is unique. ROW_NUMBER can contain duplicates.

 

What Happens If You Use Oracle ROWNUM and ORDER BY?

A common use for ROWNUM is to find the number of each row. This is often used in queries with an ORDER BY clause.

But how does this actually work?

Let’s go back to our original example.

SELECT ROWNUM, first_name, last_name, address_state
FROM student;

Result:

ROWNUM	FIRST_NAME	LAST_NAME	ADDRESS_STATE
1	John	Smith	New York
2	Susan	Johnson	Colorado
3	Tom	Capper	Nevada
4	Mark	Holloway	New York
5	Steven	Webber	New York
6	Julie	Armstrong	Texas
7	Michelle	Randall	Florida
8	Andrew	Cooper	Texas
9	Robert	Pickering	Colorado
10	Tanya	Hall	Texas
11	Jarrad	Winston	Utah
12	Mary	Taylor	Oregon
13	Mark	Anderson	California
14	John	Rogers	Nevada

You can see we have 14 records here, and the ROWNUM is in sequential order. There is no ordering of the other records.

Now, let’s add an ORDER BY to this query, to order by the last name.

SELECT ROWNUM, first_name, last_name, address_state
FROM student
ORDER BY last_name;

Result:

ROWNUM	FIRST_NAME	LAST_NAME	ADDRESS_STATE
13	Mark	Anderson	California
6	Julie	Armstrong	Texas
3	Tom	Capper	Nevada
8	Andrew	Cooper	Texas
10	Tanya	Hall	Texas
4	Mark	Holloway	New York
2	Susan	Johnson	Colorado
9	Robert	Pickering	Colorado
7	Michelle	Randall	Florida
14	John	Rogers	Nevada
1	John	Smith	New York
12	Mary	Taylor	Oregon
5	Steven	Webber	New York
11	Jarrad	Winston	Utah

The data has been ordered by the last_name value, but the ROWNUM is not in order.

This is because the ROWNUM happens just before the ORDER BY.

What if we use a WHERE clause? We can show only students that have an address_state value of Nevada or New York.

According to the results earlier, these students had ROWNUM values of 1, 3, 4, 5, and 14.

SELECT ROWNUM, first_name, last_name, address_state
FROM student
WHERE address_state IN ('Nevada', 'New York');

Result:

ROWNUM	FIRST_NAME	LAST_NAME	ADDRESS_STATE
1	John	Smith	New York
2	Tom	Capper	Nevada
3	Mark	Holloway	New York
4	Steven	Webber	New York
5	John	Rogers	Nevada

You can see that we have our 5 records here. But, the ROWNUM values are different. This is because the Oracle ROWNUM is applied after the WHERE clause. There is only records 1 to 5 – there is no record 14.

Now let’s order this data.

SELECT ROWNUM, first_name, last_name, address_state
FROM student
WHERE address_state IN ('Nevada', 'New York')
ORDER BY last_name;

Result:

ROWNUM	FIRST_NAME	LAST_NAME	ADDRESS_STATE
2	Tom	Capper	Nevada
3	Mark	Holloway	New York
5	John	Rogers	Nevada
1	John	Smith	New York
4	Steven	Webber	New York

We get a similar outcome as before. The data is ordered by the last_name value, and the ROWNUM is not in order.

 

Using ROWNUM with Subqueries

A more appropriate way to use the ROWNUM pseudocolumn is with a subquery.

The basic steps are:

1. Write your query
2. Order your query
3. Enclose this query within a subquery
4. Filter the outer query using ROWNUM

Let’s see an example. Say we wanted to see the students who are in the top 5 when ordered by last_name.

One way to do this might be:

SELECT ROWNUM, first_name, last_name, address_state
FROM student
WHERE ROWNUM <= 5
ORDER BY last_name;

Result:

ROWNUM	FIRST_NAME	LAST_NAME	ADDRESS_STATE
3	Tom	Capper	Nevada
4	Mark	Holloway	New York
2	Susan	Johnson	Colorado
1	John	Smith	New York
5	Steven	Webber	New York

However, this is not correct, because the ROWNUM is used to limit the number of rows, and then the ORDER BY is done. This shows the top 5 in the table before the ORDER BY.

To do this correctly, enclose it in a subquery as mentioned earlier.

SELECT ROWNUM, first_name, last_name, address_state
FROM (
  SELECT first_name, last_name, address_state
  FROM student
  ORDER BY last_name
) sub
WHERE ROWNUM <= 5;

Result:

ROWNUM	FIRST_NAME	LAST_NAME	ADDRESS_STATE
1	Mark	Anderson	California
2	Julie	Armstrong	Texas
3	Tom	Capper	Nevada
4	Andrew	Cooper	Texas
5	Tanya	Hall	Texas

This now shows the student records, ordered by last_name, and then only the top 5, which is the result we were looking for.

So, that’s what Oracle ROWNUM and Oracle ROW_NUMBER does, and how they are different. Be careful when you use the ORDER BY as well, as it may not give you the results you need.