0

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.

Vishal Suthar
  • 17,013
  • 3
  • 59
  • 105
payel ghosh
  • 51
  • 1
  • 4
  • 7

3 Answers3

4

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.

Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
3

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
Tanner
  • 22,205
  • 9
  • 65
  • 83
  • 1
    It's not so simple if you want to show that view for example in a client app like MS-Excel. Then i like to apply the order-by in the view directly. – Tim Schmelter Nov 12 '14 at 13:57
  • @TimSchmelter perhaps, but the OP hasn't specified how the data is going to be used – Tanner Nov 12 '14 at 13:59
3

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.

Saravana Kumar
  • 3,669
  • 5
  • 15
  • 35