Post reply

why order by clause is not working in CTE?

  • March 4, 2012 at 11:39 pm

    #256909

    Hi,

    Why Order By clause is not working with CTE ?. I know this is a restriction with CTE. Anybody know about the logical reason behind this?

  • March 4, 2012 at 11:50 pm

    #1454643

    Same reason for views , because an order clause in a CTE is irrelevant. The order of the returned result set is defined by the ORDER clause in the SELECT statement.


    Clear Sky SQL
    My Blog[/url]

  • March 5, 2012 at 12:02 am

    #1454646

    thanks for the reply.

    But still I didn't get it. Can you please explain with example?:-)

  • March 5, 2012 at 12:28 am

    #1454651

    A cte like a table should be considered as a placeholder ( an array in which data i stored for further manipluation e.g order by) for the data.

    As such; these objects consider only the data and therefore do not assign any order to it.

    Its the reason why we can't predict which order rows will be returned when you run select * from table.

    Unless explicitly required using an " order by" clause sql assume the data need not be fetched in any particular order from these objects and uses any order it sees fit.

    Jayanth Kurup[/url]

  • March 5, 2012 at 3:16 am

    #1454702

    Order By is only valid in the outer-most query (the one that queries the CTE) unless a row-limiting TOP is in place.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply