How does ORDER BY work when there are multiple rows with the same name?
I executed a query:

SELECT * FROM XYZ ORDER BY NAME;

On Oracle I get the following result:

On SQL Server I get the following result

Why is a different sequence shown? In Oracle the row with NULL appears 2nd whereas in SQL Server it appeared 1st. Is there any default behavior for each DBMS?

Is there any way to make SQL Server's result look like Oracle's?

CodePudding user response：

An unordered result set is delivered with a non-deterministic ordering and it is possible that the order can change with every execution of the query.

If you apply an ORDER BY clause that defines a total ordering of the result set then that result set will be delivered in a deterministic ordering and the output will be identical with every execution of the query (assuming the underlying data set is unchanged).

If you apply an ORDER BY clause that defines a partial ordering of the result set then the results set will be partly ordered and partly non-deterministic.

For example, if you have the data set:

and you use:

SELECT *
FROM   table_name
ORDER BY name, value

Then that defines a total ordering of the result set such that it will be initially ordered by name, which defines a partial ordering, and then, within each set of rows with the same name, will be ordered by the value and the values are unique. This outputs:

If you do not define a total ordering:

SELECT *
FROM   table_name
ORDER BY name

Then the rows will be ordered by name but within the set of rows with the same names the individual rows can be in ANY order and this is a partial order and could output:

but equally could output:

Is there any way to make SQL Server's result look like Oracle's?

Do not apply a partial ordering; add columns to your ORDER BY clause to make it a total ordering and then the RDBMSes should output the result set in the same order.

SELECT *
FROM   XYZ
ORDER BY
       NAME ASC,
       UID  ASC NULLS LAST;