CODEWITHSUNDEEP
postgresql
Sergo
Sergo

Reputation: 35

Should I do ORDER BY twice when selecting from subquery?

I have SQL query (code below) which selects some rows from subquery. In subquery I perform ORDER BY.

The question is: will order of subquery be preserved in parent query? Is there some spec/document or something which proves that?

SELECT sub.id, sub.name, ot.field 
  FROM (SELECT t.id, t.name
          FROM table t
         WHERE t.something > 10
      ORDER BY t.id
         LIMIT 25
) sub
LEFT JOIN other_table ot ON ot.table_id = sub.id
/**order by id?**/```

Upvotes: 0

Views: 81

Answers (1)

user330315
user330315

Reputation:

will order of subquery be preserved in parent query

It might happen, but you can not rely on that.

For example, if the optimizer decides to use a hash join between your derived table and other_table then the order of the derived table will not be preserved.

If you want a guaranteed sort order, then you have to use an order by in the outer query as well.

Upvotes: 1

Related Questions