KB Issue

You may receive an error when creating a View with a SQL query. The ORDER BY Clause is invalid in views.

Issue 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 Sales, 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 a new VIEW using the following code.

[SQK Server - Create New View]

CFFCS | CarrzSynEdit: | SQL Script

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.

[SQK Server - Create New View]

CFFCS | CarrzSynEdit: | SQL Script

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.