Apparently, it is not possible to use Oracle’s pseudo column rownum to select rows after a given number of rows, e.g. like
-
SELECT * FROM tab WHERE rownum > 100;
or in MySQL
-
SELECT * FROM tab LIMIT 100,50;
Oracle’s explanation for this (from the DB reference: ROWNUM):
The first row fetched is assigned a ROWNUM of 1 and makes the condition false. The second row to be fetched is now the first row and is also assigned a ROWNUM of 1 and makes the condition false. All rows subsequently fail to satisfy the condition, so no rows are returned.
However, to skip the first X rows in your query, you can make it a subquery like this (be sure to alias rownum!):
-
SELECT * FROM
-
(SELECT field1, field2, ROWNUM rn FROM tab)
-
WHERE rn > 100;
