Since line numbers are assigned sequentially for rows that are retrieved and returned.
This is how your work works. It captures the first line of the candidate and temporarily gives it line number 1, which does not match your state, so it is discarded.
Then you get the second candidate line, and line number 1 is also set (since the previous one was discarded). It also does not match.
Then the third line of the candidate ... well, I'm sure you can see where this is happening now. In short, you will never find a string that satisfies this condition.
Line numbers are only useful for = 1 , < something or <= something .
All of this is explained in Oracle docs for the rownum pseudo-column .
You should also keep in mind that SQL is a relational algebra that returns unordered sets if you don't specify an order. This means that line number ten can be something else and something else in three minutes.
If you want (kludgy, admittedly) a way to get the nth row, you can use something like (for the fifth row):
select * from ( select * from ( select col1, col2, col3 from tbl order by col1 asc ) where rownum < 6 order by col1 desc ) where rownum = 1
Internal selection ensures that you have a sequential order of the query before you start throwing rows, and the average selection will throw everything except the first five rows out of it, and also reverse the order.
The outer selection will then only return the first line of the reverse set (which is the last line of the five-row set when it was in ascending order).
Perhaps the best way:
select * from ( select rownum rn, col1, col2, col3 from tbl order by col1 ) where rn = 5
This works by restoring everything and assigning rownum to a “real” column, and then using that real column number to filter the results.