2

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.

9

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:


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.

  • The last bullet point convinced me specifically because it discusses PostgreSQL, which prompted me to ask the question in the first place. Thank you! Oh, the quotes from the standard that you added are even better! – aoven Sep 10 '17 at 18:46
  • iám assuming the "resultset" in this quote from the SQL standard counts for anny kind of derived table also the one you run in the ... FROM ( SELECT ... ORDER BY ...) AS alias.. I read something in the MariaDB documentation that using ORDER BY in these subqueries was not allowed by SQL standard.. So it's is pretty confusing to say the least.. – Raymond Nijland Feb 24 at 22:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.