Oracle: SELECT top N rows skipping the first X rows

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;

Über Stefan

Polyglot Clean Code Developer

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    
    Markdown is turned off in code blocks:
     [This is not a link](http://example.com)

To create not a block, but an inline code span, use backticks: 

Here is some inline `code`.

For more help see http://daringfireball.net/projects/markdown/syntax