Solved

How can i get row number from oracle table

Posted on 2000-02-28
3
1,149 Views
Last Modified: 2012-05-04
I want to get the record number from the oracle table , i have ever used rownum and rowid before but it does not work because rownum can use only with "<"  (lower than) sign i can not use "=" or ">"  ,i want the case that can fix that the data must be in the record 50-100 or something like this because i must manipulate the data that have more than 280,000 record , thanks for advance
0
Comment
Question by:kanatera
3 Comments
 
LVL 6

Accepted Solution

by:
crsankar earned 150 total points
ID: 2567571
You can do this

CREATE VIEW EMPVIEW AS
SELECT EMPNO, ENAME, ROWNUM AS RECORD_NUM
FROM EMP


SELECT * FROM EMPVIEW;

MPNO ENAME      RECORD_NUM
---- ---------- ----------
7369 SMITH               1
7499 ALLEN               2
7521 WARD                3
................


SELECT * FROM EMPVIEW
WHERE RECORD_NUM >= 7 AND RECORD_NUM <= 10
/

MPNO ENAME      RECORD_NUM
---- ---------- ----------
7782 CLARK               7
7788 SCOTT               8
7839 KING                9
7844 TURNER             10

0
 
LVL 4

Expert Comment

by:sganta
ID: 2567665
Yes, I agree with crsankar

But, you don't have to create view, You can always make use of INLINE query.
I am afraid because data is large.

In anyway you can try this query.

SELECT * FROM ( SELECT col1,col2,ROWNUM rw_num
                             FROM    table1)
WHERE rw_num BETWEEN 20 AND 30;

Or

You can issue the following query to make it affective & efficient

Suppose the &upper_bound and &lower_bound are the bound records

SELECT * FROM  ( SELECT col1,col2,ROWNUM rw_num
                              FROM    table1
                              WHERE ROWNUM <= &upper_bound)
WHERE rw_num BETWEEN &lower_bound  AND &upper_bound;


Hope this helps you !
0
 

Author Comment

by:kanatera
ID: 2567814
thanks you very much now i can use it now,it's very pleasure from you
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question