Using oracle Rownum with between keyword

I am planning to use JDBC Pagination with Oracle (Query based technique ) No caching of results . When i tried to use ro开发者_Python百科wnum 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 ??

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.

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.

SELECT *

FROM (

   SELECT rownum   AS rowCount ,ID,UNIT_NAME
   FROM   MWT_COMPANY_UNIT  )  

WHERE rowCount BETWEEN requiredRowNumMinYouWant AND requiredRowNumMaxYouWant