× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.


As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.