Using oracle Rownum with between keyword

Question

I am planning to use JDBC Pagination with Oracle (Query based technique ) No caching of results . When i tried to use rownum with between Option , it didn't gave me any results

select * from mytable where rownum between 10 and 20;

But this gave me results .

select * from mytable where rownum < 20;
Please tel me How to solve this ??

score 4 · Answer 1 · answered Sep 06 '11 at 09:58

I have just answered a very similar question, one way of approaching it would be to do this:

select *
from
( select rownum rnum, a.*
from (your_query) a
where rownum <= :M )
where rnum >= :N;

Providing a little wrapper for rownum.

I dont think this is wise for large volume implementations however. Although i haven't tested it.

score 1 · Answer 2 · edited May 23 '17 at 10:24

1

See this question for an explanation of why BETWEEN does not work with ROWNUM and Oracle & Pagination, and this one for how to perform pagination in Oracle queries.

edited May 23 '17 at 10:24

Community

1
1

answered Sep 06 '11 at 09:42

Tony Andrews

129,880
21
220
259

RoshanS · Answer 3 · 2019-07-20T17:19:32.980

0

SELECT *

FROM (

   SELECT rownum   AS rowCount ,ID,UNIT_NAME
   FROM   MWT_COMPANY_UNIT  )

WHERE rowCount BETWEEN requiredRowNumMinYouWant AND requiredRowNumMaxYouWant

edited Jul 20 '19 at 17:19

answered May 23 '19 at 10:45

RoshanS

89
2
5

Using oracle Rownum with between keyword

3 Answers3