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