Order by in View

An ORDER BY clause without TOP or FOR XML specification is not allowed in the query

defining the view because a view is supposed to represent a table. A table is a logical entity

that has no order to its rows.

So names must be unique in valid table and the names must be unique.

Ex:

IF OBJECT_ID('VCustomers', 'V') IS NOT NULL

DROP VIEW VCustomers;

GO

CREATE VIEW VCustomers

AS

SELECT custid, custname

FROM Customers

order by custid

GO

It throws error below

Msg 1033, Level 15, State 1, Procedure VCustomers, Line 5 [Batch Start Line 3]

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.

TOP and ORDER BY or ORDER BY and FOR XML are allowed in a view definition, whereas ORDER BY alone is not.

Now modify the view as below.

CREATE VIEW VCustomers

AS

SELECT top(10) percent custid, custname

FROM Customers

order by custid

GO

Now run the below script

When you are done run the below script to delete the view.

IF OBJECT_ID('VCustomers', 'V') IS NOT NULL

DROP VIEW VCustomers;

GO

For More details watch the video below.