SQL Paged query

Question

I've the following SQL structure:

SELECT * FROM (
    SELECT subquery.* FROM (
        SELECT some_columns
        FROM some_tables
        WHERE junctions_critereas
        GROUP BY some_columns
      UNION
        SELECT some_columns
        FROM some_tables
        WHERE junctions_critereas
        GROUP BY some_columns) subquery
    GROUP BY some_columns)
WHERE ROWNUM > startRecord AND ROWNUM <= endRecord;

• If I exclude the ROWNUM criterea the query returns 33 records.
• If I put startRecord = 0 and endRecord = 10 the query returns the 10 first records.
• If I put startRecord = 0 and endRecord = 20 the query returns the 20 first records.
• If I put startRecord = 0 and endRecord = 40 the query returns all the 33 records.
• If I put startRecord = 10 and endRecord = 20 the query returns no records.

If I change the final WHERE to WHERE ROWNUM BETEEW startRecord AND endRecord I got the same results.

Some one knows what's happening here?

Answer 1

(Note: I'm assuming that the first condition is really ROWNUM < startRecord as @RToyota points out in a comment.)

The Oracle documentation is very clear about this:

Conditions testing for ROWNUM values greater than a positive integer are always false. For example, this query returns no rows:

SELECT * FROM employees
    WHERE ROWNUM > 1;

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.

In Oracle 12c, you can use OFFSET/FETCH NEXT. In earlier versions, you can either use row_number() or nest queries with offsets.