Rownum between clause not working - Oracle

Question

I have a table:

table1

user_id
1
2
3
4

When I run this query:

SELECT 
rownum, user_id
FROM 
table1 where rownum between 1 and 4;

But when I run, it does not work:

SELECT 
rownum, user_id
FROM 
table1 where rownum between 2 and 4;

What am I doing wrong?

Answer 1

rownum is incremented as the result set is generated. If the value "1" is never generated, then "2" is never generated.

Because you want to return the row number, I would recommend using row_number():

select seqnum, user_id
from (select t1.*, row_number() over (order by ?) as seqnum
      from table1 t1
     ) t1
where seqnum between 2 and 4;

The ? is for the column that specifies the order of the result set.

SQL tables represent unordered sets. So your original query is functionally equivalent to:

select (1 + rownum), userid
from table1
where rownum <= 3;

Because the ordering is not specified. With a specified ordering, you can use row_number().

In Oracle 12C+, you can also express this as:

select rownum, userid
from table1
offset 1 row fetch first 3 rows only;