CFF KB - Carrz-Fox-Fire Promotions Knowledge Base

CFF KB is all about 1 thing: The Sharing of Knowledge and the Power we gain from it.

Knowledge Base

Breadrumbs:
>>Development (Web and Desktop) >> Database >> Microsoft SQL Server Management Studio
The ORDER BY clause is invalid in views

Share With Friends ^{(Updated 6-8-2010)}

Article ID:
7269
Date Created
Friday, March 1, 2024
This Article Has been Viewed
305 times
Short Desc
You may receive an error when creating a View with a SQL query. The ORDER BY clause is invalid in views.
Details
You must use the TOP Clause when creating a SQL Query in SSMS View. When using the ORDER BY Clause, if not, you will receive the following error.

Msg 1033, Level 15, State 1, Procedure Header_AlbumSales, Line 3 [Batch Start Line 0]
The ORDER BY Clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.
Recreate Issue
You will receive this error when creating the new VIEW with the following code.

Create View MyViewName_Here
as
Select Col2 from Table1 where Col1=1 order by Col1 Desc

Even though this code will run in SSMS and will run on your website, it will not work in VIEW.
Resolve Issue
In most cases, you will have to use a TOP Clause to add the Query to SSMS View when using a Group By.

Create View MyViewName_Here
as
Select Top 1 Col2 from Table1 where Col1=1 order by Col1 Desc

Having TOP 1, or whatever number you need, will allow the code to run in VIEW.

All Topics