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.

Msg 1033, Level 15, State 1, Line 13
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.

     Here the message is straight clear, it says that you cannot use the ORDER BY clause in any of the views, inline functions, derived tables, subqueries, and CTE. But still you can use it if you are also using TOP or FOR XML in your query.
Let's create a table and understand the scenarios:

---- Create Table
UserMaster




CREATE TABLE UserMaster

(

 UserID              VARCHAR(20)

,UserName            VARCHAR(100)

,ParentCode          VARCHAR(20)

,JoiningMonth        INT

,JoiningYear         INT

)




---- Insert data
in UserMaster




INSERT INTO UserMaster

SELECT 'U000','ADMIN','BACKEND',10,2010

UNION

SELECT 'U001','User1','U000',10,2011

UNION

SELECT 'U002','User2','U001',10,2011

UNION

SELECT 'U003','User3','U001',10,2011

UNION

SELECT 'U004','User4','U001',5,2012

UNION

SELECT 'U005','User5','U000',5,2012

UNION

SELECT 'U006','User6','U005',5,2012

UNION

SELECT 'U007','User7','U005',6,2012

UNION

SELECT 'U008','User8','U005',6,2012

UNION

SELECT 'U009','User9','U007',6,2012

 1) Using CTE :

See the below script which is using plain CTE:

;WITH CTE

AS

(

       SELECT *

       FROM UserMaster

       ORDER BY
UserID

)

SELECT * FROM CTE

As you are usning here ORDER BY UserID in CTE, so it will give you the exception stated above.
Now if you change the script as below:

;WITH CTE

AS

(

       SELECT TOP 5 *

       FROM UserMaster

       ORDER BY
UserID

)

SELECT * FROM
CTE

 This executes well without any error because here we used TOP 5 clause.
And now Run the below script:

WITH CTE

AS

(

 SELECT STUFF((SELECT ','+CAST(UserID AS VARCHAR(MAX))

              FROM
UserMaster

              ORDER BY UserID

              FOR
XML PATH('')),1,1,'') AS CSV

)

SELECT * FROM CTE

Here also the script executes without an error, because we used FOR XML clause.

 2) Using VIEW :

 Now we try to create a veiw which uses ORDER BY clause as below:

CREATE VIEW DBO.vw_TestView

AS

SELECT * 

FROM   UserMaster

ORDER BY USERID

Again the Server throws the same exception stated above.
But if you change the script as below:

CREATE VIEW DBO.vw_TestView

AS

SELECT TOP 5 * 

FROM   UserMaster

ORDER BY USERID

 It executes well without any issue.

 3) Using DERIVED TABLES:

 Execute the below script with ORDER BY clause:

SELECT M.* 

FROM   UserMaster M

       INNER JOIN
(SELECT * FROM UserMaster

                WHERE ParentCode = 'U005'

                ORDER BY USERID) S

       ON M.USERID = S. USERID

Output will be the same error as above
Use TOP 2 and then run the script:

SELECT M.* 

FROM   UserMaster M

       INNER JOIN
(SELECT TOP 2 * FROM UserMaster

                WHERE ParentCode = 'U005'

                ORDER BY USERID) S

       ON M.USERID = S. USERID

 Now it works well and gives the Output.

 4) Using SUBQUERIES:

 Also in Subqueries, if we use ORDER BY then it will throw the exception. See the below script:

SELECT * 

FROM   UserMaster

WHERE  USERID IN (SELECT USERID FROM UserMaster

              WHERE ParentCode = 'U005'

              ORDER BY USERID)

 Use TOP 2 clause and then run the script:

SELECT * 

FROM   UserMaster

WHERE  USERID IN (SELECT TOP 2 USERID FROM
UserMaster

              WHERE ParentCode = 'U005'

              ORDER BY USERID)

This time it runs successfully and gives the output.







Reference: Govind Badkur(http://govindbadkur.blogspot.com)