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].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: