Re: sql v opnqryf

On 09-Mar-2012 05:31 , Michael Schutte wrote:

I believe that the order by inside a CTE is ignored.

i.e. with this query, it's still in RRN order.

WITH cte as (
SELECT rrn(build) rrn, build.*
FROM build
ORDER BY BUNAME)
SELECT *
FROM cte

Likely a query /rewrite/ would be found to have eliminated the CTE for that query request, versus merely having eliminated\ignored the ORDER BY in the CTE. The query implementation likely would have generated no [un]ordered temporary result, regardless of the request to do so. The optimizer would find little value in implementing the ordering of that temporary result, nor even the creation of that temporary result, in order to plan the means to access the data for the outer-most SELECT [which requests neither any specific collation\ORDER nor any selection\WHERE which might benefit from having implemented with an ordered temporary result].

Regards, Chuck