vinhdh
asked on
ROWNUM?
Hi,
How to execute SQL command with (ROWNUM > i and ROWNUM <j) expression?
Thanks and best rgds,
Vinh, DangHa
How to execute SQL command with (ROWNUM > i and ROWNUM <j) expression?
Thanks and best rgds,
Vinh, DangHa
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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>)
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;
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.
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.
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);
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);
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.