Database Implementations of ORDER BY in a Subquery

Question

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

http://en.wikipedia.org/wiki/Order_by states:

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?

Answer 1

You're going to have to make your application not put the ORDER BY inside the subquery (maybe it has an option to not use a needless subquery in the first place). As you've already discovered, this syntax is not supported in SQL Server without TOP. And with TOP, unless you want to leave some rows out, using TOP 100 PERCENT is going to render the ORDER BY optimized away anyway.

And in Oracle and PostGres, just because the syntax is supported, does not mean it is obeyed. And just because you observe it as being obeyed in some scenario, does not mean that it will continue to be obeyed as new versions come out or with subtle changes to your data, statistics, the query itself, or the environment.

I can assure you that, without a doubt, if you want a guarantee about order, you need to put the ORDER BY on the outermost query. This should be a doctrine you hold close no matter what platform you're using.

You are asking for a link that officially states that something is not supported. This is like looking in your car owner's manual for an official statement that your car cannot fly.

Answer 2

I admit this is sleazy but if you're in a pinch try returning the top number of rows in the subquery. Returning the top 100 percent doesn't work but if you want to go through the trouble you can query the number of rows and pass that to TOP as a variable. I tested this on a database set to compatability level 80 so I think it should work with SQL 2000.

SELECT * FROM (
        SELECT TOP (100000) ID, GEOM, Name
        FROM t
        ORDER BY Name
        ) as tbl