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.

I am creating Employee table in sql server:

CREATE TABLE Employee(

    Id BIGINT IDENTITY PRIMARY KEY,

    Name VARCHAR(50),

    Location VARCHAR(20)

)

If we will create view

1.

CREATE VIEW vwEmployee

AS(

    SELECT Id,Name

    FROM Employee

    WHERE Location = 'USA'

    ORDER BY Name

)

Or we are writing select query either using common table expression or form derived table :

2.

WITH cteEmployee

AS(

    SELECT Id,Name

    FROM Employee

    WHERE Location = 'USA'

    ORDER BY Name

)SELECT * FROM cteEmployee

3.

SELECT * FROM(

    SELECT Id,Name

    FROM Employee

    WHERE Location = 'USA'

    ORDER BY Name

) AS DerivedTable

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.

Cause: It is due to ORDER BY clause.

Solution: If we want to use ORDER BY clause in view, cte or derived table we must have to user either TOP clause or FOR XML clause

Alternative to write above quires are:

1.

CREATE VIEW vwEmployee

AS(

    SELECT TOP(100) PERCENT Id,Name

    FROM Employee

    WHERE Location = 'USA'

    ORDER BY Name

)

2.

WITH cteEmployee

AS(

    SELECT TOP(100) PERCENT Id,Name

    FROM Employee

    WHERE Location = 'USA'

    ORDER BY Name

)SELECT * FROM cteEmployee

3.

SELECT Col FROM(

    SELECT Id,Name

    FROM Employee

    WHERE Location = 'USA'

    ORDER BY Name

    FOR XML AUTO

) AS DerivedTable(Col)