Re: ORDER BY in VIEW not working

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

.

Relevant Pages

  • Re: The Many Flavors of SQL - Can a SQL Server query work in MS Access?
    ... One thing is that if you have more than one join in the From clause you have to use Parentheses ... You might try building the base of the query using the query grid. ... inner join cmsopen.dbo.apm_vendor v ... in the Query Analyzer on a Windows 2000 Server box running SQL Server ...
    (microsoft.public.access.queries)
  • Re: Can anyone help with Data Designer issue?
    ... Hitchhiker's Guide to Visual Studio and SQL Server ... debug what the command is sending to the Access database for any ... This query uses parameters and I'm afraid it's not sending the where ... as soon as I put in this where clause: ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Can anyone help with Data Designer issue?
    ... Hitchhiker's Guide to Visual Studio and SQL Server ... debug what the command is sending to the Access database for any ... This query uses parameters and I'm afraid it's not sending the where ... as soon as I put in this where clause: ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: sort order in query changes on one SQL Server but not another
    ... > What I don't understand is why on one SQL Server it works consistently ... There is no guarantee of order - EVER- for a query that ... >>> sort order on queries that do not specify an ORDER BY clause? ...
    (microsoft.public.sqlserver.server)
  • Re: CONTAINS performance
    ... That said, and with the query plan, I can start to give you more ... relational join in the context of the free-text optimization, ... SQL Server tables. ...
    (microsoft.public.sqlserver.fulltext)