Can one retrieve only rows X to Y from a table?

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 <>

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.

Related Documents

How does one escape special characters when writing SQL queries?

Can one select a random collection of rows from a table?

How does one eliminate duplicates rows from a table?

Can one retrieve only the Nth row from a table?

How can one dump/ examine the exact content of a database column?

How does one add a column to the middle of a table?

How does one select EVERY Nth row from a table?

How does one select the LAST N rows from a table?

How does one select the TOP N rows from a table?

What is the difference between VARCHAR, VARCHAR2 and CHAR data types?

Map/ concatenate several rows to a column

How does one code a matrix/crosstab/pivot report in SQL?