Is order in a subquery guaranteed to be preserved?

Question

I am wondering in particular about PostgreSQL. Given the following contrived example:

SELECT name FROM
  (SELECT name FROM people WHERE age >= 18 ORDER BY age DESC) p
LIMIT 10

Are the names returned from the outer query guaranteed to be be in the order they were for the inner query?

Answer 1

No, put the order by in the outer query:

SELECT name FROM
  (SELECT name, age FROM people WHERE age >= 18) p
ORDER BY p.age DESC
LIMIT 10

The inner (sub) query returns a result-set. If you put the order by there, then the intermediate result-set passed from the inner (sub) query, to the outer query, is guaranteed to be ordered the way you designate, but without an order by in the outer query, the result-set generated by processing that inner query result-set, is not guaranteed to be sorted in any way.

Answer 2

For simple cases, @Charles query is most efficient.

More generally, you can use the window function row_number() to carry any order you like to the main query, including:

• order by columns not in the SELECT list of the subquery and thus not reproducible
• arbitrary ordering of peers according to ORDER BY criteria. Postgres will reuse the same arbitrary order in the window function within the subquery. (But not truly random order from random() for instance!)
  If you don't want to preserve arbitrary sort order of peers from the subquery, use rank() instead.

This may also be generally superior with complex queries or multiple query layers:

SELECT name
FROM  (
   SELECT name, row_number OVER (ORDER BY <same order by criteria>) AS rn
   FROM   people
   WHERE  age >= 18
   ORDER  BY <any order by criteria>
   ) p
ORDER  BY p.rn
LIMIT  10;