CODEWITHSUNDEEP
sqlpostgresqlsubquerysql-order-by
knpwrs
knpwrs

Reputation: 16466

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?

Upvotes: 19

Views: 9158

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657002

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 p.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;

Upvotes: 12

Charles Bretana
Charles Bretana

Reputation: 146499

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.

Upvotes: 21

Aditya
Aditya

Reputation: 2246

The are not guaranteed to be in the same order, though when you run it you might see that it is generally follows the order.

You should place the order by on the main query

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

Upvotes: -2

Related Questions