Posted by David Portas on 01/30/06 09:18

Doug wrote:

> you guys all missed it.
>
> his first example is two ordered tables that are then unioned. In his
> business problem, the final result is not ordered.
>
> In his second example, he is ordering a table.
>
> In order to solve his businss problem, the solution is:
>
> CREATE VIEW vwRouteReference
> AS
> select * from
> (
> SELECT tblItem.ID,
> tblItem.Numb + ' - ' + tblQuestion.DescrPrimary AS FullName,
> tblItem.Numb, tblQuestion.DescrPrimary AS Type
> FROM tblItem INNER JOIN tblQuestion
> ON (tblItem.ID = tblQuestion.Item_ID)
> WHERE (((tblItem.Category_ID)>0))
> UNION
> SELECT tblItem.ID,
> tblItem.Numb + ' - ' + tblItem.Type + ' : ' + tblItem.Class AS
> FullName,
> tblItem.Numb, tblItem.Type + ' : ' + tblItem.Class AS Type
> FROM tblItem
> WHERE (((tblItem.Type) = 'Assignment' OR (tblItem.Type) = 'Check' OR
> (tblItem.Type) = 'Route'))
> )
> ORDER BY .Numb
>
> This is the brute force way.
> Fundamentally, you are unioning two data sets, then ordering them, then
> returning them.

We didn't miss it but you did. Did you test the code you posted? Even
if you correct it, SQL Server is NOT guaranteed to repect an ORDER BY
in the view unless you use ORDER BY also when you *query* the view.
ORDER BY is therefore redundant in views as far as ordering the data is
concerned. Attempting to use it that way is an unsupported,
undocumented trick and may work only intermittently. Books Online is
quite explicit about this:

http://msdn2.microsoft.com/en-us/library/ms188723.aspx

<quote>

ORDER BY guarantees a sorted result only for the outermost SELECT
statement of a query. For example, consider the following view
definition:

CREATE VIEW TopView AS
SELECT TOP 50 PERCENT * FROM Person.Contact
ORDER BY LastName

Then query the view:

SELECT * FROM TopView

Although the view definition contains an ORDER BY clause, that ORDER BY
clause is used only to determine the rows returned by the TOP clause.
When querying the view itself, SQL Server does not guarantee the
results will be ordered, unless you specify so explicitly, as shown in
the following query:

SELECT * FROM TopView
ORDER BY LastName

</quote>

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

[Back to original message]