I have some problem sorting columns and paginate them.

for example,

select *
from
    (select A.*, ROWNUM RNUM
         from 
        (select * from USER order by name) A
        where ROWNUM <= 3 --edited
    ) B
    where RNUM >= 1

as A

and

select *
from
    (select A.*, ROWNUM RNUM
         from 
        (select * from USER order by name) A
        where ROWNUM <= 4 --edited
    ) B
    where RNUM >= 1

as B

The difference of two is columns rownums. I want to know why this happened.

A returns,

RNUM | NAME
-----------
1    | a
-----------
2    | b
-----------
3    | c

B returns.

RNUM | NAME
-----------
1    | a
-----------
2    | f     -- what happened?
-----------
3    | b
-----------
4    | c

This is just an example.

Why each columns gets different rownums because of range of rownum?

CodePudding user response：

Why what happened?

• source is select * from user (apparently, false table name; that's reserved for function that returns username of a currently logged user) so it selects all rows from the table
• rownum rnum then reflects number of all rows returned from that table, one-by-one, from 1 to total number of rows in that table
• where rownum <= 10 restricts number of rows returned by the source subquery to 10 (the 1st example) / 200 (the 2nd example). It means that
  • the 1st query works with at most 10 rows
  • the 2nd query works with at most 200 rows
• the final filter (where rnum >= 1) returns what's previously being said - at most 10 (or 200) rows

So ... what's the problem? What did you expect to get?

As of your comment: it seems that order by clause is missing:

select *
from
    (select A.*, ROWNUM RNUM
         from 
        (select * from USER order by name) A
        where ROWNUM <= 10
        order by a.name                 --> this
    ) B
    where RNUM >= 1