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:
- Write your query
- Order your query
- Enclose this query within a subquery
- 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.
Awesome information, thank you so much and because of this I subscribed and using these information in my work which is helping me to stand tall in my work.
Such an informative content, I have used rownum in business logic now your content gives me an clear idea about it. Thanks my friend 😊
Rownum if it is used in order by and i have to create the same thing in SQL SERVER how to replicate it, i have tried using row_number but the order isn’t the same