4

I am using Between condition in ORACLE, to achieve pagination. I will sort data on CNT column in descending order to display most counted value first.

I am getting different row order with normal order by and row number order by.

Below is my data set:

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 Query:

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, row #3 and row #2 are interchanging. I want to fix order. How can i do that?

Nivedika thakur
  • 41
  • 1

2 Answers2

1

Your issue is that you have two rows with the same value for cnt. This brings up the larger issue of stable sorts.

Sorting in relational databases is not stable (the answer to this question gives a good description of stable sorts). That is, when the order by keys have the same value they can appear in any order. They can appear in different orders on different runs.

This is actually obvious. SQL tables represent unordered sets. That is, there is no natural ordering, so there is no way to define a stable sort.

If you want consistency in sorts, you need to be sure that the keys uniquely define each row. I usually have an id column that can be added as the last key to the order by.

In your case, you may be able to use the pattern: order by cnt desc, pattern.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Gordon, As per my requirement I cant combine another column in sorting. Is there any other way? – Nivedika thakur Oct 10 '17 at 11:45
  • @Nivedikathakur . . . Nope. That is how sorting works in SQL. Why can't you add another column? SQL certainly allows it. – Gordon Linoff Oct 10 '17 at 11:46
  • I need to show most repeated values(cnt column) on the top. If i add another column it will disturbs the row order. – Nivedika thakur Oct 10 '17 at 11:49
  • @Nivedikathakur . . . The largest count value will always be in the first row returned -- whether or not you add an extra key. I have no idea what you mean by "disturbs the row order". The whole point is that you seem to want a stable sort. – Gordon Linoff Oct 10 '17 at 12:08
0
ORDER BY CNT DESC, PATTERN

would work in your case.

If not, a possibility might be to use the pseudocolumn ORA_ROWSCN

ORDER BY CNT DESC, ORA_ROWSCN

This would work if the records you retrieve were not batch-inserted at the same time. Not sure if you can use it in a window function though, try it

Thomas G
  • 9,886
  • 7
  • 28
  • 41