CODEWITHSUNDEEP
sql-server
payel ghosh
payel ghosh

Reputation: 51

Order BY is not supported in view in sql server

i am trying to create a view in sql server.

create view distinct_product as
select distinct name from stg_user_dtlprod_allignmnt_vw order by name;

this is showing an error.

error message is:

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.

plz help me out where i am wrong.

Upvotes: 0

Views: 9365

Answers (3)

Tim Schmelter
Tim Schmelter

Reputation: 460208

You could use TOP with a number that is greater than the number of records:

CREATE VIEW [dbo].[distinct_product]
AS
SELECT DISTINCT TOP 10000000 name
FROM  stg_user_dtlprod_allignmnt_vw 
ORDER BY name

You cannot use TOP 100 PERCENT since the optimizer recognizes that TOP 100 PERCENT qualifies all rows and does not need to be computed at all, so the ORDER BY wouldn't be guaranteed.

Upvotes: 5

Saravana Kumar
Saravana Kumar

Reputation: 3729

A view cannot be sorted with an ORDER BY clause. You need to put the ORDER BY clause into any query that references the view.

A view is not materialized - the data isn't stored, so how could it be sorted? A view is kind of like a stored procedure that just contains a SELECT with no parameters... it doesn't hold data, it just holds the definition of the query. Since different references to the view could need data sorted in different ways, the way that you do this - just like selecting from a table, which is also an unsorted collection of rows, by definition - is to include the order by on the outer query.

Upvotes: 3

Tanner
Tanner

Reputation: 22743

You can't order a view like that when it's created as the message states, unless you follow the other answers from Tim / Raphael, but you can order results selected from a view:

So create it in step 1:

create view distinct_product as
    select distinct name 
    from stg_user_dtlprod_allignmnt_vw

Then order it when you retrieve data:

select * 
from distinct_product 
order by name

Upvotes: 3

Related Questions