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:
SELECT * FROM tableX WHERE rowid in ( SELECT rowid FROM tableX WHERE rownum <= 7 MINUS SELECT rowid FROM tableX WHERE rownum < 5);
3) “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.”
SELECT * 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.
Reference : Dilip Kumar Jena ( https://sqlexplore.wordpress.com )