I am using an application (MapServer - http://mapserver.org/) that wraps SQL statements, so that the ORDER BY statement is in the inner query. E.g.
SELECT * FROM ( SELECT ID, GEOM, Name FROM t ORDER BY Name ) as tbl
The application has many different database drivers. I mainly use the MS SQL Server driver, and SQL Server 2008. This throws an error if an ORDER BY is found in a subquery.
From the MS Docs (although this is for SQL Server 2000 it still seems to apply):
When you use an ORDER BY clause in a view, an inline function, a derived table, or a subquery, it does not guarantee ordered output. Instead, the ORDER BY clause is only used to guarantee that the result set that is generated by the Top operator has a consistent makeup. The ORDER BY clause only guarantees an ordered result set when it is specified in the outermost SELECT statement.
However the same type of query when run in Postgres (9) and Oracle return results - with the order as defined in the subquery. In Postgres the query plan shows the results are sorted and the Postgres release notes include the item which implies subquery orders are used:
Avoid sort when subquery ORDER BY matches upper query
Although some database systems allow the specification of an ORDER BY clause in subselects or view definitions, the presence there has no effect.
However from my own checking of query plans:
- SQL Server 2008 does not support ORDER BY in a subquery
- Postgres 9 does support ORDER BY in a subquery
- Oracle 10g supports ORDER BY in a subquery
So my question are there any links that can officially confirm or deny that Postgres and Oracle do not allow sorting in a subquery?