1

I've got a query written something like this.....

with subquery as
(
  SELECT A, B, C, D, 
  row_number() OVER(PARTITION BY X ORDER BY Y) as RN
  FROM blah blah
  ORDER BY A,B
  LIMIT 100
)
SELECT C,D
FROM subquery
WHERE RN=1

As you can see, the subquery is sorted, but the top-level query isn't.

The top level query doesn't do anything complicated, it just selects based on the partition by clause.

Will the sort order from the subquery be maintained in the final results?

Observation of the various data-sets I've tested against indicates the sort order IS maintained, but I'm not sure if this is just a lucky coincidence or not.

2
  • In general ORDER BY in subquery/cte is ignored until the construction which affects on the result dependent by ordering (LIMIT, for example) occured.
    – Akina
    Feb 24, 2020 at 16:18
  • You have added LIMIT 100. Now this limiting will be performed for described ordering - but nevertheless the final recordset will be transferred to main query (like) in unordered state.
    – Akina
    Feb 24, 2020 at 16:59

2 Answers 2

3

In short, you should not assume that it does. The DBMS can return your sub-query in a different order without violating any rules. So even if you observe that the order is preserved for version X, that may change for version Y.

Your query contains some interesting complications, where the ordering attributes are removed from the outer query. A good optimizer may benefit from this info and return rows in a different order at the outermost level.

3

No, the order by or the sub-query will not influence the order of the outside result.

If at all, it might be influenced by the order by of the window function.

But you can never rely on that.

If you need a specific order for the outer query that you can rely on, you have to use an order by there as well.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.