database - Oracle sql query Order By gives different results -

output of

query 1:

select id users order case when deviation_level=2863 1 else 2 end 

is

800019   800030   800040   800003   800007   800015   800025   800026....etc    

output of query 2:

select id     (select id users      order case when deviation_level=2863 1 else 2 end) rownum<=16; 

is

800019 800030 800028 800020 800021 800018 800012 800161...etc 

why order changes in 2nd query? please suggest correct solution limit size of first query result.

the reason order cannot guarantee ordering on duplicate values.

in query, put deviation_level in column list of select , understand order not same when duplicate values.

for example,

query 1

sql> select empno, deptno emp order case when deptno=10 1 else 2 end;       empno     deptno ---------- ----------       7782         10       7839         10       7934         10       7566         20       7654         30       7698         30       7900         30       7788         20       7369         20       7844         30       7876         20       7521         30       7499         30       7902         20  14 rows selected. 

query 2

sql> select empno, deptno   2    3    (select empno, deptno emp order case when deptno=10 1 else 2 end   4    )   5  rownum<=5;       empno     deptno ---------- ----------       7782         10       7934         10       7839         10       7369         20       7654         30  sql> 

so, ordering in second case when rownum applied, picked randomly among similar values.

look @ first 3 ordered rows:

output 1

     empno     deptno ---------- ----------       7782         10       7839         10       7934         10 

output 2

     empno     deptno ---------- ----------       7782         10       7934         10       7839         10 

order deptno not guarantee same order every time. in above query, if want particular order, make order on column too, i.e. empno.

order empno, deptno 

if compare both outputs, there no guarantee ordering same since deptno same 10 in 3 rows. when have similar values, , if order them, picking them in random.