Search code examples
sqlpostgresqlsubquerysql-order-by

Is order in a subquery guaranteed to be preserved?

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?

Solution

  • 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.