Showing posts with label distinct. Show all posts
Showing posts with label distinct. Show all posts

Wednesday, March 28, 2012

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
--

Friday, March 9, 2012

Newbie Basic Question - DISTINCT & PKs

Could somebody please clarify this simple question for me? I'm clearly missing something basic...

I'm in the process of trying to teach myself T-SQL out of a WROX book, and am having a problem wrapping my head around this example, I understand the concept of Primary Keys through using MS Access, but haven't used TSQL extensively (or at all for that matter...)

Using the classic "Northwind" database, using this query:

SELECT COUNT(OrderID)
FROM [Order Details]

This returns 2155, which is the total number of rows with an OrderID, the primary key. Now, as I understood it, a PK has to be unique, but yet this query on the same DB:

SELECT COUNT(DISTINCT OrderID)
FROM [Order Details]

returns 830 rows. If the PK (OrderID) is unqiue, how is the DISTINCT function returning fewer rows? Wouldn't that imply there are duplicate PKs?

I'm sure there's a simple answer to this, but I just can't seem to get my head around it... Thank you so much for taking the time to explain this to a rookie!Because orderId isn't the primary key of [order details]. The PK of that table is OrderId, ProductId. Check sp_help '[Order Details] ' for the details. OrderId is a Foreign Key reference to Orders, where the PK is orderId.|||

Aaaah.... I see, I think I mis-interpreted the information I was getting from Management Studio. Thank you so much for taking the time to correct a silly mistake!