Oracle query string order is different when used between ROW_NUM

Question

I use Between Condition in ORACLE to achieve pagination. I will sort the data by CNT column in descending order to display most of the calculated values ​​first.

I get different line orders with order of order and line number.

Below is my dataset:

PATTERN_TBL

PATTERN  |  CNT
1        |  3
Aaaa Aaa |  14
aaaaaa   |  3

Normal query:

SELECT PATTERN, CNT FROM PATTERN_TBL ORDER BY CNT DESC

PATTERN  |  CNT
Aaaa Aaa |  14
aaaaaa   |  3
1        |  3

Between the request:

SELECT ROW_N, PATTERN,CNT FROM (SELECT 
 ROW_NUMBER() OVER ( ORDER BY CNT DESC) AS ROW_N,
  PATTERN, CNT FROM PATTERN_TBL) 
WHERE ROW_N BETWEEN 1 AND 100 

PATTERN  |  CNT
Aaaa Aaa |  14
1        |  3
aaaaaa   |  3

In the above two outputs, line # 3 and line # 2 change. I want to fix the order. How can i do this?

Answer 1

Your problem is that you have two lines with the same value for cnt. This leads to a larger problem of resistant varieties.

( question ). , order by , . .

. SQL . , , .

, , . , order by.

: order by cnt desc, pattern.

Answer 2

ORDER BY CNT DESC, PATTERN 

.

, , , pseudocolumn ORA_ROWSCN

ORDER BY CNT DESC, ORA_ROWSCN

, , , . , ,