Oracle ROWNUM Explained

If you were to implement a Top-N or pagination query in an Oracle database, you wouldn’t find any dedicated clause to limit the query result like TOP, LIMIT or FETCH FIRST. For each row returned by the query, Oracle provides a ROWNUM pseudocolumn that returns a number indicating the order in which the database selects the row from a table or set of joined views.

Example

Let’s take a look at the 2014 Sochi Olympics Men’s Normal Hill Individual ski jumping results in the skijump_results table. There is no index on the skijump_results table. The following queries are examples of the Top-N and pagination queries, which I will use in the following parts of the article.

1. Select the top 3 best ski jumpers, sorted by the number of points in descending order.
2. Select the 2nd to 4th ski jumpers, sorted by the number of points in descending order.

+----+--------------------+---------+--------+
| id | name               | country | points |
+----+--------------------+---------+--------+
| 32 | Michael Hayboeck   | AUT     | 258.0  |
+----+--------------------+---------+--------+
| 42 | Thomas Diethart    | AUT     | 258.3  |
+----+--------------------+---------+--------+
| 43 | Andreas Wellinger  | GER     | 257.1  |
+----+--------------------+---------+--------+
| 44 | Anders Bardal      | NOR     | 264.1  |
+----+--------------------+---------+--------+
| 49 | Peter Prevc        | SLO     | 265.3  |
+----+--------------------+---------+--------+
| 50 | Kamil Stoch        | POL     | 278.0  |
+----+--------------------+---------+--------+

ROWNUM can be used to build Top-N and pagination queries. Now, you might think you’ve got it and write the following query to select the top 3 ski jumpers sorted by the number of points in descending order:

| select *
| from skijump_results
| where ROWNUM <= 3
| order by points;

The result:

+----+--------------------+---------+--------+
| id | name               | country | points |
+----+--------------------+---------+--------+
| 32 | Michael Hayboeck   | AUT     | 258.0  |
+----+--------------------+---------+--------+
| 42 | Thomas Diethart    | AUT     | 258.3  |
+----+--------------------+---------+--------+
| 43 | Andreas Wellinger  | GER     | 257.1  |
+----+--------------------+---------+--------+

Seems right? – Not really. The tricky part is that the row number is assigned before the ordering of the query result takes place. So the above query returns the first three random records (the order of which depends on the retrieval method) and then sorts them. Knowing that, let’s fix the query:

| select * 
| from (select *
|       from skijump_results
|       order by points)
| where ROWNUM <= 3;

The result:

+----+--------------------+---------+--------+
| id | name               | country | points |
+----+--------------------+---------+--------+
| 50 | Kamil Stoch        | POL     | 278.0  |
+----+--------------------+---------+--------+
| 49 | Peter Prevc        | SLO     | 265.3  |
+----+--------------------+---------+--------+
| 44 | Anders Bardal      | NOR     | 264.1  |
+----+--------------------+---------+--------+

Finally, you get it right. Now, let’s move to a pagination query and select the 2nd, 3rd and 4th ski jumpers sorted by number of points. You already know that the row number filter should be applied after sorting the query result so you may write the following query:

| select * 
| from (select *
|       from skijump_results
|       order by points)
| where ROWNUM <= 4 and ROWNUM > 1;

The result:

+----+--------------------+---------+--------+
| id | name               | country | points |
+----+--------------------+---------+--------+

which returns no rows... what’s wrong now? It’s time to thoroughly explain the concept of ROWNUM.

ROWNUM is a pseudocolumn that gets many people into trouble (but that you already know). A ROWNUM value is not permanently assigned to a row (this is a common misunderstanding). It may be confusing when a ROWNUM value is actually assigned. A ROWNUM value is assigned to a row after it passes filter predicates of the query but before query aggregation or sorting. What is more, a ROWNUM value is incremented only after it is assigned.

This is why the above query returns no rows. The first row of the query result does not pass ROWNUM > 1 predicate, so ROWNUM does not increment to 2. For this reason, no ROWNUM value gets greater than 1, consequently, the query returns no rows.

Here is the correct query:

| select *
| from (select *, ROWNUM rnum
|       from (select *
|             from skijump_results
|             order by points)
|       where ROWNUM <= 4)
| where rnum > 1;

The result:

+----+--------------------+---------+--------+
| id | name               | country | points |
+----+--------------------+---------+--------+
| 49 | Peter Prevc        | SLO     | 265.3  |
+----+--------------------+---------+--------+
| 44 | Anders Bardal      | NOR     | 264.1  |
+----+--------------------+---------+--------+
| 42 | Thomas Diethart    | AUT     | 258.3  |
+----+--------------------+---------+--------+

This time the result is what you expected.