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.