Link to home
Start Free TrialLog in
Avatar of vinhdh
vinhdh

asked on

ROWNUM?

Hi,
How to execute SQL command with (ROWNUM > i and ROWNUM <j) expression?

Thanks and best rgds,
Vinh, DangHa
ASKER CERTIFIED SOLUTION
Avatar of doronla
doronla

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mshaikh
mshaikh

The answer to your question is:

There is NO WAY to execute SQL command with (ROWNUM > i and ROWNUM <j) expression

You can ONLY do (ROWNUM <j) in an SQL statement.

Howler, if you have any more questions regarding this.
Avatar of vinhdh

ASKER

Thanks for your help,

I execute query "SELECT * FROM myTable", it return about 500 records. I don't want to process all this records in this time. So I'd like to get about 100 records for each time.

If I use 'ROWNUM<=100', I don't know to get next 100 records for next time.

Please help me.
Hi,

 Try the foll:

select * from table_name where
rowid >=
  (select max(rowid) from fnd_application
  where
 rownum<=<lower value of the range>)
and rowid <=
  (select max(rowid) from table_name
  where
 rownum<=<Higher value of the Range>)


There's a trick to do:

select * from (select rownum my_rownum, a.* from tab a where rownum < 10) where my_rownum > 5;
If you are not going to need ORDER BY clause, that is if the order of the retrieved rows is unimportant then you can try:

SELECT column1, column2,....
FROM (SELECT ROWNUM_A, column1, column2 ...
        FROM myTable A) B
WHERE B.ROWNUM_A >= your_lower_limit
  AND B.ROWNUM_A < your_upper_limit

Now if you wanted the rows to be returned in any particular order, this won't work. If you need a particular order in the returned rows, post you columns you need ordered and I will help you with the query for that.
Avatar of vinhdh

ASKER

Thank you very much.
u could try thee following

select * from tab a  where
higher_no <
(select count(*) from tab b where
b.rowid<a.rowid)
and lower_no >=
(select count(*) from tab c
where c.rowid>=b.rowid);