Solved

How can i get row number from oracle table

Posted on 2000-02-28
3
1,138 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

705 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now