Apr 17, 2012

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)

2 comments:

  1. Anonymous4/24/2013

    Where is the exact reason?
    You have explained only work-around for OREDR BY.
    ;-)

    ReplyDelete
    Replies
    1. Exact reason is it is meaningless to specify order by clause inside views, inline functions, derived tables, subqueries, and common table expressions since we can override this order by outer order by clause that is why sql server has restricted this.

      Delete