FB

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.

Get Your SQL Cheat Sheet

Three pages of SQL commands to save/print for easy reference

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 analytic 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.

 

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.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Get Your SQL Cheat Sheet

Three pages of SQL commands to save/print for easy reference

Get Your SQL Cheat Sheets Now: