What is the order when SELECT-ing unordered set from an ordered SELECT?

Question

What does the SQL standard and its mainstream DB implementations (SQL Server, Oracle, DB2, PostgreSQL, Firebird, MySQL, etc.) say about the final order in the following general case:

SELECT * FROM (SELECT * FROM Table t ORDER BY t.Field)

Can I trust that the inner order is respected by the outer projection as well or do I need to state the ORDER BY in the outer statement explicitly, to be sure?

I understand the recommendation to "explicitly list it". However, explicit listing can cause problems in certain cases. PostgreSQL, for one, requires that all fields listed in ORDER BY be listed in SELECT DISTINCT as well. That's no good in some cases, because it changes the expected result set, which could no longer be distinct in light of the single column I'm interested in.

Answer 1

Can I trust that the inner order is respected by the outer projection as well ...?

No, you cannot.

... or do I need to state the ORDER BY in the outer statement explicitly, to be sure?

Yes, you do need to state the ORDER BY you need in the outer statement explicitly, if you want to be sure that the order that the rows are returned is what you want.

While you may - more often in old versions of some DBMS - sometimes find that the order of the rows returned is the one specified in the internal ORDER BY, there is no guarantee about it. If you want a specific order, you need to be explicit.

---

Related / similar questions:

• Why is SSMS inserting new rows at the top of a table not the bottom?
• What is the default order of records for a SELECT statement in MySQL?
• In SQL Server, can I guarantee an order without an explicit ORDER BY clause when an index seek is forced on a table with only a clustered index?
• Is it REALLY possible that the order will not be guaranteed for this particular redundant derived table?

---

Quotes from the SQL standard:

Cursors
...
A cursor in the open state identifies a result set and a position relative to the ordering of that result set. If the cursor's <cursor specification> does not simply contain an <order by clause>, or simply contains an <order by clause> that does not specify the order of the rows completely, then the rows of the result set have an order that is defined only to the extent that the <order by clause> specifies an order and is otherwise implementation-dependent.

---

<query expression>
...
5) The rows of T are ordered as follows:

a) If QE does not immediately contain an <order by clause>, then the ordering of rows in T is implementation-dependent.