Rownum with Order by

Above version 8.0, you can select rows for specified range.

select *

from ( select a.*, rownum rnum

from ( YOUR_QUERY_GOES_HERE — including the order by ) a

where rownum <= MAX_ROWS )
where rnum >= MIN_ROWS

/