SQL server order is different than oracle

1

Why order of record showing different

ORACLE

SELECT SIDE,SIZE_NB,NOZ_MARK,LENGTH (NOZ_MARK)
FROM hxdesign.NOZZLE1
WHERE JEP_NO = 'HEI9022355'
AND REV_NO = 0
AND QTY > 0
ORDER BY SIDE ,SIZE_NB DESC,LENGTH (NOZ_MARK) DESC;

Result

SIDE SIZE_NB NOZ_MARK LENGTH(NOZ_MARK)
0 24 C 1
0 2 SN1 3
0 2 SN3 3
0 2 SN2 3
0 18 E1 2
0 18 E2 2
1 2 TN1 3
1 2 TN2 3
1 10 A 1
1 10 B 1

and sql server same query showing different result

0 24 C 1
0 2 SN1 3
0 2 SN2 3
0 2 SN3 3
0 18 E1 2
0 18 E2 2
1 2 TN1 3
1 2 TN2 3
1 10 A 1
1 10 B 1

Please help me i want query result like oracle

2

Please add the length colum for SQL in the second query and show us result. You might have empty spaces in your data

3

SIDE ,SIZE_NB DESC,LENGTH(NOZ_MARK) are the same for the following:

0,2,SN1,3
0,2,SN3,3
0,2,SN2,3

As a relation/table is an unordered set, the ORDER BY will order as far as it can and then the order in both Oracle and SQL Server cannot be guaranteed.

If you want the order to be deterministic then add the PK on the end of the ORDER BY clause. In you example you could get way with adding NOZ_MARK.

1 Like
4

Based on the results, it looks to me that Oracle is not ordering properly