SQL Standard – Order When SELECT-ing Unordered Set from Ordered SELECT

order-bysql-standard

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.

Best Answer

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.

Related Solutions

Mysql – Use order from another select clause

Unless I'm grossly misunderstanding your structure, a simple join should work fine:

SELECT stories.vid, body, timestamp, COUNT(votes.id) votecnt FROM stories
INNER JOIN votes ON stories.vid=votes.vid
WHERE stories.lv=1 AND stories.status=1
GROUP BY votes.vid
ORDER BY votecnt DESC

I'm not sure what you mean by

It would be even better, if the first query would sort the table by status = 1 count at first, and then total votes.

since your where statement only uses votes/stories that have status=1.

MySQL – Default Order of Records for SELECT Statement

Reposting my answer to a similar question regarding SQL Server:

In the SQL world, order is not an inherent property of a set of data. Thus, you get no guarantees from your RDBMS that your data will come back in a certain order -- or even in a consistent order -- unless you query your data with an ORDER BY clause.

So, to answer your question:

MySQL sorts the records however it wants without any guarantee of consistency.
If you intend to rely on this order for anything, you must specify your desired order using ORDER BY. To do anything else is to set yourself up for unwelcome surprises.

This is a property of all SQL, not just MySQL. The relevant text in the SQL-92 spec is:

If an <order by clause> is not specified, then the ordering of the rows of Q is implementation-dependent.

There are similar bits of text in the spec for cursors.

Best Answer

Related Solutions

Mysql – Use order from another select clause

MySQL – Default Order of Records for SELECT Statement

Related Question