Menu
If I have a few UNION Statements as a contrived example:
SELECT * FROM xxx WHERE z = 1 UNION SELECT * FROM xxx WHERE z = 2 UNION SELECT * FROM xxx WHERE z = 3 What is the default order by behaviour?
The test data I'm seeing essentially does not return the data in the order that is specified above. I.e. the data is ordered, but I wanted to know what are the rules of precedence on this.
Another thing is that in this case xxx is a View. The view joins 3 different tables together to return the results I want.
998
By default, SQL Server sorts out results using ORDER BY clause in ascending order. Specifying ASC in order by clause is optional.
Within UNION each SELECT statement must have the same columns number. The columns must have analogous data types. In each SELECT statement, the columns must be in the same order.
The ORDER BY keyword is used to sort the result-set in ascending or descending order. The ORDER BY keyword sorts the records in ascending order by default.
Ascending is the default sort order in an ORDER BY clause.
There is no default order.
Without an Order By clause the order returned is undefined. That means SQL Server can bring them back in any order it likes.
EDIT: Based on what I have seen, without an Order By, the order that the results come back in depends on the query plan. So if there is an index that it is using, the result may come back in that order but again there is no guarantee.
191
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
