[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

How can i get row number from oracle table

Posted on 2000-02-28
3
Medium Priority
?
1,156 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 450 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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
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.
Via a live example, show how to take different types of Oracle backups using RMAN.

650 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