Newbie Question about views

I have created a view by using following statement:-

ALTER VIEW vw_ctotalweeks AS
select DISTINCT allweek from hr_depts WHERE allweek > DateAdd(day,
-28, GetDate()) order by allweek desc

But I am getting follwoing error message:-

The ORDER BY clause is invalid in views, inline functions, derived
tables, and subqueries, unless TOP is also specified.

My question is, can I use Order by class in Views if not, how can sort
dates
in view

Please helpViews are like tables - their data is not logically ordered, which is why
ORDER BY isn't valid.

The way to sort a view is in the SELECT statement when you retrieve data
from it:

SELECT allweek
FROM vw_ctotalweeks
ORDER BY allweek DESC

--
David Portas
----
Please reply only to the newsgroup
--|||It is possible to order data in a view use the following select statment:

SELECT TOP 100 PERCENT *
FROM Orders
ORDER BY CustomerID

The top 100 percent option makes the order by posible|||...so the create view statement would look something like this:

Create view v_orders as
SELECT TOP 100 PERCENT *
FROM Orders
ORDER BY CustomerID|||Some people do suggest using this "trick" to order views. I believe that
there are good reasons to avoid doing this.

This behaviour of TOP in a view is undocumented or at least,
under-documented. The ORDER BY is valid only for the purpose of defining the
TOP x PERCENT so intuitively you would not expect it to apply to the result
of a SELECT from the view. 99% of the time it *may* work but there is no
guarantee that it will always continue to work.

A view is supposed to behave like a table - without a logical order.
Sometimes you don't want the view to be sorted. Consider this example:

CREATE TABLE foo (x INTEGER PRIMARY KEY NONCLUSTERED, y INTEGER NOT NULL)

GO

CREATE VIEW foo_view
AS
SELECT TOP 100 PERCENT x,y
FROM foo
ORDER BY y

GO

SELECT x FROM foo_view

The most efficient plan for the SELECT x query is an index scan of the
nonclustered index. The optimiser therefore has a choice either to ignore
the ORDER BY and retrieve an unsorted result set or to force a sort which
gives a sub-optimal execution plan.

As always with specific engine behaviour, results could change between
different installations, service packs or versions of SQLServer, which could
break your code if it relies on an undefined feature.

In short, don't use undocumented tricks as a substitute for good design and
if you do use this feature be aware of its limitations and risks.

SELECT * FROM view ORDER BY ...

Hope this helps.

--
David Portas
----
Please reply only to the newsgroup
--