Re: Create view using CTE as to have an ORDER BY

[CRPence <CRPbottle@xxxxxxxxx>]

On 05 Jun 2013 09:15, Vern Hamberg wrote:
> This makes me wonder what is the point of ORDER BY in a CTE

  I already noted in a /for example/ in my prior reply [which came 
later than the above quoted message], but I offer additionally:

  The effects of an ordered CTE can have an impact on the overall 
result set; i.e. the selection of rows.  Most notably, in combination 
with use of the FETCH FIRST [N ROWS] clause to limit the amount of 
/ordered/ rows, though there may be others.  Generally there is little 
or no sense without collation, for a truncated set, except /random/ 
sampling; i.e. a truncated intermediate result set likely will be an 
effective /ranking/ for which only the top or bottom N-rows will be of 
interest to the query.

> - unless the optimizer can use it.<<SNIP>>

  The optimizer /could/ use the ORDER BY clause in the CTE to decide on 
an implementation, although is under no obligation to do so.  The query 
engine needs only effect the proper results; i.e. it need not effect any 
implied ordering of the intermediate results.  Only the order of the 
final result set for the outer-most\final SELECT, according to its ORDER 
BY and Sort Sequence (SRTSEQ), is an obligation to the query engine.  As 
noted in my prior reply to the OP, the specific query given is likely 
rewritten to remove the CTE, to avoid implementation with an actual 
temporary result [aka derived] table; but of course, while maintaining 
the selectivity defined within the CTE.