Solved

How can i get row number from oracle table

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
oracle query help 36 84
PL/SQL Search for multiple strings 5 40
Bash Script to Analyze Oracle Schemas 11 84
Oracle - SQL Where clause causing Invalid Number Error 4 28
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
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…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

867 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

22 Experts available now in Live!

Get 1:1 Help Now