Guaranteed to store an order in a subquery?

Question

I am interested 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 in the order in which they were for the inner query?

Answer 1

No, put the order in the outer query:

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

The internal (auxiliary) query returns the result. If you place an order there, then the intermediate result set transferred from the internal (auxiliary) request to the external request will be guaranteed to be ordered as you designated it, but without order in the external request, the result is the set created by processing this internal set of query results is not guaranteed to be sorted in any way.

Answer 2

For simple cases, the most effective @Charles query .

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

• column order is not in the SELECT list of the subquery and therefore not reproducible
• random peer order according to ORDER BY criteria. Postgres will reuse the same random order in the window function inside the subquery. (But not really random order from random() for example!)
  If you do not want to maintain an arbitrary sort order of peers from a subquery, use rank() instead.

It can also be generally excellent 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; 

Answer 3

is not guaranteed in the same order, although when you run it you can see that it usually follows the order.

You must place an order at the basic request

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