Mar 17, 2014

ORDER BY clause in common table expression in sql server


We cannot use ORDER BY clauses in common table expression directly in sql server. For example:  

WITH cteStudent
AS(
    SELECT * FROM Student ORDER BY 1
)UPDATE cteStudent SET Stu_Name = 'Scott'

We will get error message like :

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

Solution: 

We must have to use either TOP or FOR XML. For example:

WITH cteStudent
AS(
    SELECT TOP(1) * FROM Student ORDER BY 1

No comments:

Post a Comment