Posted by David Portas on 03/09/06 08:55

Doug wrote:
> >ORDER BY is valid in views only when used in conjunction with TOP
>
> but if i have an order by in a view, without the TOP, it compiles.
>

That would indeed be a bug but I'm not aware of it. Can you post some
code to reproduce that behaviour and state what version, edition and
service pack you are using? What you should see is an error message.
Following example tested on Dev Ed. SQL Server 2000 - 8.00.2039 SP4:

CREATE TABLE T1 (x INT);
GO
CREATE VIEW v1 AS SELECT x FROM T1 ORDER BY x
GO

Result:

Server: Msg 1033, Level 15, State 1, Procedure v1, Line 1
The ORDER BY clause is invalid in views, inline functions, derived
tables, and subqueries, unless TOP is also specified.

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