Thread: order is preserved by outer select?

Here's another easy (stupid?) question:

  SELECT data.* FROM (SELECT * FROM foo ORDER BY bar) AS data

Will the returned rows still be ordered by bar?

--
Make April 15 just another day, visit http://fairtax.org

Joshua N Pritikin wrote:
> Here's another easy (stupid?) question:
>
>   SELECT data.* FROM (SELECT * FROM foo ORDER BY bar) AS data
>
> Will the returned rows still be ordered by bar?

In practice, for this precise query, yes.

However, embedding this into a larger query or adding a WHERE clause
could re-order the results. For example, if PG uses a hash anywhere
during the query you'll lose ordering.

More importantly, the specifications say that without an ORDER BY the
order of a result-set is undefined. So if the developers find it's more
efficient to destroy order they are free to do so. That means you might
upgrade and have your application stop working.

So - good practice says don't rely on ORDER BY except in the outermost
level of your query.

--
   Richard Huxton
   Archonet Ltd