Creat view invalid cause by order

Question

Please my friend, any suggestion?

create view prodottiaustralia as 
select sum(salesamount),
 CalendarYear, sum(orderquantity), p.EnglishProductName from FactInternetSales i inner join DimProduct p on p.ProductKey=i.ProductKey 
 inner join   dimdate d on d.DateKey=i.DueDateKey inner join DimCustomer o 
 on i.CustomerKey=o.CustomerKey inner join DimGeography g on o.GeographyKey=g.GeographyKey where g.EnglishCountryRegionName='australia' group by CalendarYear, i.ProductKey, p.EnglishProductName order by i.ProductKey, CalendarYear asc

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.

Answer 1

You can not have Order by in View. If you wan the data to be sorted, you may do it in outside.

Select * From prodottiaustralia order by <column>

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 2

Yes, this is True, that you cannot use ORDER BY inside a VIEW Definition, unless you are using a TOP, OFFSET, or a FOR XML Clause.

Still, if you want to use ORDER BY in your QUERY, add a TOP 100 PERCENT to it.

example

CREATE VIEW prodottiaustralia AS

SELECT TOP 100 PERCENT sum(salesamount), CalendarYear.......

FROM .....

GROUP BY .....

Please mark this as ANSWER if it helps...













---

Ashu_Blueray

Answer 3

You cannot specify ORDER BY clause in a view unless otherwise a TOP clause is specified in the query.

You can use the ORDER BY clause when you query the view like below:

create view prodottiaustralia as 
select 
	sum(salesamount) AS salesamount,
	CalendarYear, 
	sum(orderquantity) AS orderquantity, 
	p.EnglishProductName
	,i.ProductKey
from FactInternetSales i inner join DimProduct p on p.ProductKey=i.ProductKey 
 inner join   dimdate d on d.DateKey=i.DueDateKey inner join DimCustomer o 
 on i.CustomerKey=o.CustomerKey 
 inner join DimGeography g on o.GeographyKey=g.GeographyKey 
 where g.EnglishCountryRegionName='australia' 
 group by 
	CalendarYear, i.ProductKey, p.EnglishProductName 
GO
SELECT * FROM prodottiaustralia ORDER BY ProductKey, CalendarYear asc
GO

---

Krishnakumar S

Answer 4

Exactly what the error message says. A view is just like a table un ordered object. If you want a query that produced data with a certaing order, use a stored procedure.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 5

>>Still, if you want to use ORDER BY in your QUERY, add a TOP 100 PERCENT to it.

You can add an order by using TOP 100%, however the order by is limited to TOP and the result set from the view may have a different ordering. 

http://msdn.microsoft.com/en-us/library/ms187956.aspx

• Important
  The ORDER BY clause is used only to determine the rows that are returned by the TOP or OFFSET clause in the view definition. The ORDER BY clause does not guarantee ordered results when the view is queried, unless ORDER BY is also specified in the query itself.

Regards
Satheesh

Answer 6

Still, if you want to use ORDER BY in your QUERY, add a TOP 100 PERCENT to it.

But why would you to that?

The view is still unordered, and if you want to query the view and get an ordered result, you need to add ORDER BY to your query.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 7

Still, if you want to use ORDER BY in your QUERY, add a TOP 100 PERCENT to it.

example

CREATE VIEW prodottiaustralia AS

SELECT TOP 100 PERCENT sum(salesamount), CalendarYear.......

According to the Books Online (http://msdn.microsoft.com/en-us/library/ms187956.aspx):

"Important

The ORDER BY clause is used only to determine the rows that are returned by the TOP or OFFSET clause in the view definition. The ORDER BY clause does not guarantee ordered results when the view is queried, unless ORDER BY is also specified in the query itself."

So although this hack will allow the view to be created, it does not provide actual ordering of results as DIEGOCTL presumably needs.  If data are returned in sequence, it is only by happenstance.  ORDER BY must be specified in the query that selects from the view to guarantee ordering.

---

Dan Guzman, SQL Server MVP, http://www.dbdelta.com

Answer 8

Try the below and see the result for NOT ordered one.

create Table T1(Col1 int)
Insert into T1 Select 10
Insert into T1 Select 1
Insert into T1 Select 100
Go
Create View vw
as
Select Top 100 PERCENT * From T1 Order by Col1 asc
go
Select * From Vw
Drop table t1
Drop view vw

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 9

"DIEGOCTN" wrote in message news:713defda-c910-40cc-97f1-196115b78f2e...

Please my friend, any suggestion?

create view prodottiaustralia as 
select sum(salesamount),
 CalendarYear, sum(orderquantity), p.EnglishProductName from FactInternetSales i inner join DimProduct p on p.ProductKey=i.ProductKey 
 inner join   dimdate d on d.DateKey=i.DueDateKey inner join DimCustomer o 
 on i.CustomerKey=o.CustomerKey inner join DimGeography g on o.GeographyKey=g.GeographyKey where g.EnglishCountryRegionName='australia' group by CalendarYear, i.ProductKey, p.EnglishProductName order by i.ProductKey, CalendarYear asc

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.

 

Have you tried: select Top (100) percent sum(salesamount), etc

---

Harry