CodeSnip: Selecting the nth Record from a Table Using Oracle
page 1 of 3
Published: 01 Feb 2008
Abstract
In this code snippet, Deepankar examines the usage of Oracle's ROWNUM keyword to select the nth row of a table. It also deals with the other possible methods of selection along with the risks involved in using them. The article begins with a short introduction followed by relevant SQL code with explanation and screenshots.
by Deepankar Sarangi
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 34909/ 68
Article Contents:

Introduction

In order to follow the article you will require Oracle 9i or an above version. ROWNUM is a virtual column, which is available in a query.


View Entire Article

User Comments

Title: superb explanation   
Name: santy
Date: 2012-07-09 11:31:48 AM
Comment:
Good explanation......
Title: Mr   
Name: Bhavanarayana
Date: 2011-01-24 2:01:02 AM
Comment:
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
Comment:
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
Comment:
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
Comment:
1.How to get nth record without help rownum?
Title: Finding nth employee   
Name: Abhishek
Date: 2009-12-02 2:31:12 AM
Comment:
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
Comment:
/* 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
Comment:
It is helpful
Title: Answer to sanjeet's question   
Name: Deepankar Sarangi (Auther)
Date: 2008-11-27 2:35:45 AM
Comment:
select * from emp where rownum <16;
    EMPNO ENAME      ROWID
---------- ---------- ------------------
     7369 SMITH      AAAHW7AABAAAMUiAAA
     7499 ALLEN      AAAHW7AABAAAMUiAAB
     7521 WARD       AAAHW7AABAAAMUiAAC
     7566 JONES      AAAHW7AABAAAMUiAAD
     7654 MARTIN     AAAHW7AABAAAMUiAAE
     7698 BLAKE      AAAHW7AABAAAMUiAAF
     7782 CLARK      AAAHW7AABAAAMUiAAG
     7788 SCOTT      AAAHW7AABAAAMUiAAH
     7839 KING       AAAHW7AABAAAMUiAAI
     7844 TURNER     AAAHW7AABAAAMUiAAJ
     7876 ADAMS      AAAHW7AABAAAMUiAAK
     7900 JAMES      AAAHW7AABAAAMUiAAL
     7902 FORD       AAAHW7AABAAAMUiAAM
     7934 MILLER     AAAHW7AABAAAMUiAAN

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
Comment:
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-2017 ASPAlliance.com  |  Page Processed at 2017-02-21 7:04:13 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search