Following error is encountered when view is attempted to created with ORDER BY clause in it. ORDER BY clause is not allowed in views in SQL Server 2005. This solution also displays the workaround to use ORDER BY in VIEW. I really do not prefer to use views. My views on SQL Views read it SQL SERVER – Restrictions of Views – T SQL View Limitations.
Msg 1033, Level 15, State 1
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.
This is error interested me as workaround of this involves some programming changes how the view is called in T-SQL query.
Fix/Solution/WorkAround:
Do not use ORDER BY clause in View when not using TOP or FOR XML in SELECT clause. If you have to use ORDER BY use it in SELECT clause where view is used.
Let us understand using simple example on AdventureWorks database.
Following script will generate error in Query Editor.
CREATE VIEW vTestContactView
AS
SELECT FirstName, LastName
FROM Person.Contact
ORDER BY FirstName DESC
GO
Msg 1033, LEVEL 15, State 1, PROCEDURE vTestContactID, Line 5
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
.
Now there is are two ways to fix the error.
Method 1: The Smart One and works all the time.
If you do not want to change your all query which is using this view do following changes in view. It will return the same result without much (or none in most cases) performance loss. This is really helpful when ORDER BY Clause uses column which is not in SELECT clause(second example).
----First example
CREATE VIEW vTestContactView
AS
SELECT TOP 100 PERCENT FirstName, LastName
FROM Person.Contact
ORDER BY FirstName DESC
GO
----Second example
CREATE VIEW vTestContactIDView
AS
SELECT TOP 100 PERCENT FirstName, LastName
FROM Person.Contact
ORDER BY ContactID DESC
GO
Method 2: The Technically Correct but only works when you have to ORDER BY on clause which is used in SELECT of view.
First create view changing without using ORDER BY clause.
CREATE VIEW vTestContactID
AS
SELECT FirstName, LastName
FROM Person.Contact
GO
Call the VIEW using
ORDER BY clause.
SELECT *
FROM vTestContactID
ORDER BY FirstName DESC
I really enjoyed writing the solution for this problem.
Reference : Pinal Dave (https://blog.sqlauthority.com)
8 Comments. Leave new
It is worth mentioning that Method 1 will only work in SQL Server 2000 or SQL Server 2005 with a hotfix, traceflag and compatibility 80. Granted, in 2005 in won’t kick out an error when you use TOP (100) PERCENT … ORDER BY but without the hotfix the query engine will actually ignore it and return the result unordered.
See KB 926292 for information on restoring the ORDER BY behavior that is present in SQL Server 2000
Example-2 really helped. Mine had all sort of pivots and stuffs and a bit more complicated but managed to get it working the way it is supposed with Example-2. Great!
Can someone help mi with this my query. I have same error what you describe :
id,
meter_id,
meter_serialnumber,
timestamp,
transponder_offset,
reading,
readingstatus_des,
consumer_firstname,
consumer_lastname,
transpondertype,
readingzone_zone,
readingzone_subzone,
meter_remark,
Hi Pinal,
My requirement is first execute Order By and after that get some records between them for Pagging.
Select emp1.* from (
select * from emp order by username
) as emp1 where emp1.empID > 10 or emp1.empID < 20
How Can I Do this ?
Thanks
Mitesh Darji
HERO!!!!!
I was looking the hole day for that solution! SELECT TOP 100 PERCENT solved it for me
Method 1 is VERY helpfull!!
Thanks a lot.
you can use :
CREATE VIEW vw_Demo
AS
select
col1,
col2,
ROWNUMER() OVER (ORDER BY col3 Desc ) As RowIndex
from t1
Thank u :)