This content has been marked as final. Show 5 replies
SELECT col1, col2, col3 FROM
(SELECT col1, col2, col3, ROWNUM r FROM table)
WHERE r BETWEEN 5AND 10;
The above query doesn't work when you want to order the records by some column and fetch a range of rows from it.
For such scenario, the following can be used.
create table test_rownum(id number(5), name varchar2(20));
select rownum, name from (select name from test_rownum order by name) where rownum <=4 MINUS select rownum, name from (select name from test_rownum order by name) where rownum <=2
Message was edited by:
The preferred solution is to make use of the analytic function ROW_NUMBER. There is a description in the Oracle documentation. The final example shown returns only the fifty-first through one-hundredth row of the EMPLOYEES table:
An alternative is to use:
SELECT last_name FROM (SELECT last_name, ROW_NUMBER() OVER (ORDER BY last_name) R FROM employees) WHERE R BETWEEN 51 and 100;
which is discussed on asktom.oracle.com. This works from Oracle 8.1 onwards.
select * from ( select a.*, rownum rnum from ( YOUR_QUERY_GOES_HERE -- including the order by ) a where rownum <= MAX_ROWS ) where rnum >= MIN_ROWS;