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

Question

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.

Answer 2

ROWNUM is a pseudocolumn and it's value will not be available at the time of query execution. So you can't filter your results based on this column. With that said, there are work arounds to mitigate it. Please see below (it is not recommended to use if your result is very huge).

Select a.* From 
(
  Select COL1, COL2, ROWNUM RowNumber From MyTable
) a
Where
   RowNumber = 5;

Answer 3

Alternative is to use MINUS

SELECT * FROM records 
WHERE   ROWNUM <= 10
minus SELECT * FROM records 
WHERE   ROWNUM <= 5

this will filter out non-unique values so you better be selecting id.

Hope this saves you some time.