CODEWITHSUNDEEP
sqlsql-servert-sqlsql-order-by
dmoney
dmoney

Reputation: 881

When no 'Order by' is specified, what order does a query choose for your record set?

I was always of the impression that a query with no specified 'Order by' rule, would order this by the results by what was specified within your where clause.

For instance, my where clause states:

WHERE RESULTS_I_AM_SEARCHING_FOR IN
ITEM 1
ITEM 2
ITEM 3

I would have imagined that the results returned for items 1, 2 and 3 would be in the order specified in the where, however this is not the case. Does anyone know what order it sorts them in when not specified?

Thanks and sorry for the really basic question!

Damon

Upvotes: 58

Views: 39728

Answers (2)

marc_s
marc_s

Reputation: 754598

If you don't specify an ORDER BY, then there is NO ORDER defined.

The results can be returned in an arbitrary order - and that might change over time, too.

There is no "natural order" or anything like that in a relational database (at least in all that I know of). The only way to get a reliable ordering is by explicitly specifying an ORDER BY clause.

Update: for those who still don't believe me - here's two excellent blog posts that illustrate this point (with code samples!) :

Upvotes: 74

Curtis
Curtis

Reputation: 103368

With SQL Server, if no ORDER BY is specified, the results are returned in the quickest way possible.

Therefore without an ORDER BY, make no assumptions about the order.

Upvotes: 30

Related Questions