Oracle/SQL: Why does query "SELECT * FROM records WHERE rownum >= 5 AND rownum <= 10" - return zero rows

Question

Hi,

Why does the following query return 'zero' records:

SELECT * FROM records WHERE rownum >= 5 AND rownum <= 10

     OR

SELECT * FROM records WHERE rownum >= 5 

Whereas the following query return proper records:

SELECT * FROM records WHERE rownum <= 15

Regards,
- Ashish

Answer 1

In Oracle, Rownum values are assigned after the filtering stage of the query - they are not rows of the table, they are rows of the query result set.

So the first row that comes back will always be given rownum 1, the second row that comes back rownum 2, etc.

The rownum value is only incremented after it is assigned, so any query like

select * from t where ROWNUM > 1

will never return any results. This query says 'I dont want to see the first row that gets returned to me, only the ones after that' which is sortof a paradox so nothing gets returned.

See Ask Tom:On ROWNUM and Limiting Results for more details.