1

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?

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
dang
  • 2,342
  • 5
  • 44
  • 91

2 Answers2

2

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;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • If I don't specify order by, would Oracle give me different results? – dang May 31 '19 at 11:07
  • @dang . . . If you don't specify the `order by`, then the ordering of the rows in the result set is indeterminate and can differ from run to run. – Gordon Linoff May 31 '19 at 17:22
1

The first row to return has number 1.
This doesn't match the criterion so it will not be returned.
The same criterion applies for each row.
Therefore, no rows will be returned.

Robert Kock
  • 5,795
  • 1
  • 12
  • 20