If you do a simple SELECT-WHERE on a CTE that is already sorted, are your results guaranteed to still be in that same order, just filtered?

Wondering about expected/deterministic ordering output from Oracle 11g for queries based on sorted CTEs.

Consider this (extremely-oversimplified for the sake of the) example SQL query. Again, note how the CTE has an ORDER BY clause in it.

WITH SortedArticles as (
    SELECT.  *
    FROM     Articles
    ORDER BY DatePublished
)
SELECT *
FROM   SortedArticles
WHERE  Author = 'Joe';

Can it be assumed that the outputted rows are guaranteed to be in the same order as the CTE, or do I have to re-sort them a second time?

Again, this is an extremely over-simplified example but it contains the important parts of what I’m asking. They are…

1. The CTE is sorted
2. The final SELECT statement selects only against the CTE, nothing else (no joins, etc.), and
3. The final SELECT statement only specifies a WHERE clause. It is purely a filtering statement.

Answer

The short answer is no. The only way to guarantee ordering is with an ORDER BY clause on your outer query. But there is no need to sort the results in the CTE in that situation.

However, if the sort expression is complex, and you need sorting in the derived CTEs (e.g. because of using OFFSET/FETCH or ROWNUM), you could simplify the subsequent sorting by adding a row number field to the original CTE based on its sort criteria and then just sorting the derived CTEs by that row number. For your example:

WITH SortedArticles as (
    SELECT *,
           ROW_NUMBER() OVER (ORDER BY DatePublished) AS rn
    FROM Articles
)
SELECT *
FROM SortedArticles
WHERE Author = 'Joe'
ORDER BY rn