ROWNUM gets values like 1,2,3,4…N where N is the maximum number of rows used in the query. Assigning unique numbers to each row/record is a runtime process. ROWNUM is never associated with any record permanently. Therefore, one cannot ask for the 7th row of a table directly. So it is not sensible to ask for the following code snippet.

Listing 1

select * from emp where rownum > n;

Also, at times people find difficulty in understanding when the ROWNUM stuff actually gets generated. The ROWNUM gets generated only after the query passes through a particular phase, but before any aggregation or sorting. ROWNUM gets incremented only after it is assigned. ROWNUM can be used as shown below.

Listing 2

select * from emp where rownum < n;

This will show any records with the matched criteria. Where n is any integer.

But ROWID is like a pointer that holds the address to each record of a table. They are unique identifiers for rows in a table. If we delete and reinsert a row, its rowid may change. If we delete a row, then Oracle may reassign its ROWID to a new row inserted later.

Listing 3 – Selection of nth record

select * from emp where rownum =1 and rowid not in 
(select rowid from emp where rownum < n);

In order to reach for nth record of emp table, the query first discards the top n-1 records and then selects the top record from the remaining list.

Let me explain with the following example.

Let us say we require querying from emp table which is a default one in Oracle 9i. Consider the first 15 records.

Listing 4

select * from emp where rownum <16;

Figure 1

Let us associate our records with respective rowid.

Listing 5

select employee_id,first_name,rowid from emp where rownum <16;

Figure 2

Go for the third record in the table. Clearly it seems that the FIRST_NAME is Lex with EMPLOYEE_ID 102 having ROWID AAAIRPAAJAAACaUAAC.

Our query given in Listing 1 works fine as long as the remaining records (after excluding the top n-1 records) appear in a sequential fashion. But there is no guarantee that the query will behave the same. We may get the wrong record if the desired sequence is not followed.

This may be seen from the following query.

Listing 6

select EMPLOYEE_ID,FIRST_NAME,ROWID from emp where rowid not in 
(select rowid from emp where rownum < 3) and rownum < 16;

Figure 3

We can clearly conclude from above that the above set of rowid must contain 3rd rowid, AAAIRPAAJAAACaUAAC, in the beginning. Any failure in having 3rd rowid in the beginning will not show the 3rd row as the output. This is simply because rownum =1 will always pick the record that appears first.

This also reflects from the query itself. As expected the query will not give the 3rd row, instead it will pick John having rowid AAAIRPAAJAAACaUAAK as it appears on top.

Listing 7

select * from emp where rownum =1 and rowid not in 
(select rowid from emp where rownum < 3);

Figure 4

Now let us find another solution.

Listing 8 – Selection of nth record (Better Alternative Logic)

select * from emp where rownum < n+1 minus select * from emp where rownum < n;

This fetches the first n records and then rejects first n-1 records to get the nth record.

Now we will verify the table emp for the 3rd record.

Listing 9

select * from emp where rownum < 4 minus select * from emp where rownum < 3;

Figure 5

And indeed we can reach 3rd record through the above code.