CodeSnip: Selecting the nth Record from a Table Using Oracle
page 2 of 3
by Deepankar Sarangi
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 37769/ 40
Article Contents:


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.

View Entire Article

User Comments

Title: superb explanation   
Name: santy
Date: 2012-07-09 11:31:48 AM
Good explanation......
Title: Mr   
Name: Bhavanarayana
Date: 2011-01-24 2:01:02 AM
Hi.. this is a wonderful help... Plz do continue and share your knowledge.... Thank u very much for posting this online..Wish you all the best
Title: nice explanation   
Name: thousef
Date: 2010-12-29 3:41:00 PM
it is a very nice method to understand the rowid,rownum queries..
Title: Good explanation   
Name: G.Gokul
Date: 2010-10-30 9:29:49 PM
Yes,this is a very good explanation about rownum.And it is a very useful tip for me to understand about the rownum and rowid concept.
Title: Question   
Name: PrashantBilaiya
Date: 2010-01-21 6:13:10 AM
1.How to get nth record without help rownum?
Title: Finding nth employee   
Name: Abhishek
Date: 2009-12-02 2:31:12 AM
Tihs works out.....
U simply make an inline view with rownum as a column and filter using that column

Select * from
Select e.*, rownum AS Record_NUM from employees e)
where record_num=24
Title: Find Top nth record of employee   
Name: Alok Kumar Ranjan
Date: 2009-10-06 10:46:36 AM
/* we can easily find Top nth record of Employee */

select * from emp
where rownum <= '&n';
Title: how to delete nth record from a table   
Name: shiba
Date: 2009-07-18 9:56:12 AM
It is helpful
Title: Answer to sanjeet's question   
Name: Deepankar Sarangi (Auther)
Date: 2008-11-27 2:35:45 AM
select * from emp where rownum <16;
---------- ---------- ------------------

select * from emp where rownum <8 minus select * from emp where rownum <7;

    EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
     7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
Title: question   
Name: sanjeet
Date: 2008-05-27 1:19:09 AM
Question: display the 7th record of emp table with using rownum.

Question: delete all duplicate records except one record from emp table as empno. , ename, salary,

Community Advice: ASP | SQL | XML | Regular Expressions | Windows

©Copyright 1998-2024  |  Page Processed at 2024-04-15 6:24:21 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search