Oracle ROWNUM lessthan works, greaterthan does not work

Question

seeing a weird problem. SQL1/SQL2 below works correctly but SQL3 does not return any rows. Any idea why?

SQL1 below return 900 rows:

select t1.*, ROWNUM from (select * from myTable where fk = 1234) t1 

SQL2 below correctly returns 1->199 rows as expected.

select t1.*, ROWNUM from (select * from myTable where fk = 1234) t1 where ROWNUM < 200;

SQL3 does not return any rows. why is this? Very confused.

select t1.*, ROWNUM from (select * from myTable where fk=1234 ) t1 where ROWNUM > 200;

Answer 1

Because ROWNUM is calculated when you reference it, so if you reference it in that WHERE clause, it counts the first row it sees as 1, the next row as 2, etc.. but by saying ROWNUM > [anything but 0] you are preventing row 1 from being selected, which prevents the counter ever getting beyond 0... it'll never get to 1 because as soon as it does, you are asking it to ignore it.

Never use ROWNUM > anything. Always use =, =< or <. You can, however, nest it in a query block and use the outer query block to apply that predicate. That way, ROWNUM is already calculated on the full set of unrestricted rows before you then ask to remove the first 200.

Example:

   select * 
     from (select myTable.*,
                  ROWNUM seq 
             from myTable 
            where fk=1234 ) 
     where seq > 200;