9

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?

  • 2
    Just because you observe certain results does not make them guaranteed. If you want consistency, put the order on the outside. Period. – Aaron Bertrand Nov 18 '14 at 13:46
  • Ideally this is what will be implemented. However to get to this stage will involve changes to the core logic and many database drivers. As this issue hasn't been reported in many years it seems as though some dbs do consistently implement ORDER BY in subqueries. It would be nice to know which ones if possible. – geographika Nov 18 '14 at 14:04
  • 2
    @geographika Even if some DBMS do so consistently till now, there is no guarantee they will continue to do the same in the future. As an example, MySQL's improvements of the optimizer in 5.6 (and MariaDB 5.3) would identify the ORDER BY in the subquery as redundant and not do the unnecessary sorting. – ypercubeᵀᴹ Nov 18 '14 at 15:43
13

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.

  • Thanks. I think MSSQL has the right approach in throwing an error. Both supporting and implementing sorting on inner queries, when it goes against a core SQL principle, seems a recipe for disaster. Not sure about the car analogy though - you need to add looking for it in the manual while the car is actually flying.. – geographika Nov 25 '14 at 10:41
-1

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
  • I did try this originally and it seemed to sort fine for small datasets. However when I was getting very large recordsets the sorting became random again in SQL Server 2008R2. Maybe relates to memory/page sizes? – geographika Nov 18 '14 at 20:08
  • Sorry it didn't help. Selecting the top 100 percent also caused the sorting to revert to random. – DBNull Nov 18 '14 at 21:13
  • This won't work if the query goes parallel, especially if Name is non-unique. It may not continue to work serially if the optimizer chooses a different index, with different key column order. – Erik Darling Oct 12 '18 at 14:01

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.