SELECT * FROM (
SELECT ename, rownum rn
FROM emp WHERE rownum < 101 ) WHERE RN between 91 and 100 ;
Note: the 101 is just one greater than the maximum row of the required rows (means x= 90, y=100, so the inner values is y+1).
SELECT rownum, f1 FROM t1
GROUP BY rownum, f1 HAVING rownum BETWEEN 2 AND 4;
Another solution is to use the MINUS operation. For example, to display rows 5 to 7, construct a query like this:
WHERE rowid in (
SELECT rowid FROM tableX
WHERE rownum <= 7 MINUS SELECT rowid FROM tableX WHERE rownum <>
this one was faster for me and allowed for sorting before filtering by rownum. The inner query (table A) can be a series of tables joined together with any operation before the filtering by rownum is applied."
FROM (SELECT a.*, rownum RN
FROM (SELECT *
FROM t1 ORDER BY key_column) a
WHERE rownum <=7) WHERE rn >=5;
Please note, there is no explicit row order in a relational database. However, this query is quite fun and may even help in the odd situation.