Clash Royale CLAN TAG#URR8PPP

Rownum not working properly [duplicate]

This question already has an answer here:

I have two tables X and Y.
when I run below query for table X

select * from
( select rownum as rn, A.* from X as A order by A.IDdesc) where rn between 0
and 1000000;


I get result as

rn Id name
1 1 xxx
2 2 yyy
3 4 zzz


but for Y table when same query is executed I get result as

select * from
( select rownum as rn, A.* from Y as A order by A.IDdesc) where rn between 0
and 1000000;

rn Id name
5 1 xxx
7 2 yyy
10 4 zzz


rn in Y table is getting some random numbers. Not able to understand this behavior. ny help would be appreciated.

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

1 Answer
1

The ROWNUM pseudo-column is added to the selected columns in the order Oracle retrieves the rows and then the ORDER BY clause is applied. What you appear to want is to order the rows first and then generate the ROWNUM against this ordering but that is not what your query will do.

ROWNUM

ORDER BY

ROWNUM

For table X, it happens that Oracle retrieves the rows in the same order as the id column. However, for table Y, the rows are not retrieved in the same order as the id column so the ROWNUM is in the order the rows were retrieved and then a different ordering is applied so the ROWNUM values appear shuffled.

id

id

ROWNUM

ROWNUM

What you should do is to apply the ORDER BY before generating the ROWNUM pseudo-column so they are in-sync:

ORDER BY

ROWNUM

SELECT *
FROM (
SELECT rownum as rn,
A.*
FROM (
SELECT *
FROM X
ORDER BY ID desc
) A
)
WHERE rn BETWEEN 0 AND 1000000;