I have a view that joins several tables, just to simplify a frequently called query. This view has an ORDER BY clause on a calculated field (the order will never be different). (on SQL Server Express 2008 R2).
The basic idea is like this (simplified):
SELECT [EventType].Name,
[EventType].TotalOccurrences,
[Session].ID,
[Session].TotalOccurrences,
[Session].TotalOccurrences / [EventType].TotalOccurrences AS saturation
FROM [EventType]
INNER JOIN [Session] ON [EventType].ID = [Session].Event
ORDER BY saturation
This view will never be called without a where clause though (which in my case would almost return the entire database). It will always be with WHERE [EventType].ID = x
. However as soon as I add the where clause, the ORDER BY clause is ignored! Without the where clause it is working as expected.
Actually a different question but I'm throwing it in here while I'm at it, is there a better way to retrieve such info? The [EventType].Name
and [EventType].TotalOccurrences
will be identical for all returned rows, which is a small waist of network bandwidth. Not that that is a problem, but I'm wondering if anything exists to tackle that without the latency overhead of multiple round-trips to the database?
[Edit] The above sample was over-simplified, the calculated field has other factors so that it would return a different order than simply ORDER BY [Session].TotalOccurrences
as it would in this example, but the principle should be clear.
[Edit2] From the answer I come to the conclusion that the Graphical Designer has more features than the underlying database:
TOP
andORDER BY
from your view. They don't belong there. Put theORDER BY
on the query that references the view.