Solved

How can i get row number from oracle table

Posted on 2000-02-28
3
1,150 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

740 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