Posted by David Portas on 03/08/06 11:40

RickW wrote:
> >>You probably don't want the DBMS to sort unless the order finds it's
> way to the top.
>
> Definitely.
>
> What I thought you were setting up was this:
>
> View A could specify an Order By clause, but it would not guarantee an
> ordered result set.
>
> Then View B could either SELECT x, y, z... FROM A and not invoke the
> ordering in the result set or SELECT x, y, z...FROM A ORDER BY
> _ORDER_OF_A_, and that would force the result set to return ordered by
> the specification in View A.
>
> I think the "SELECT * FROM V ORDER BY ORDER OF V => C1 ASCENDING" view
> is what I'm calling View B, the second one that's needed in order to
> invoke the ordering.
>
> I was thinking my suggestion would avoid needing B to force the
> ordering.
>
> For you is the thing I call View B a query from outside the database
> tool, such as a SQL statement passed in from a client, like in the
> example you pointed to with your link to the 4 techniques?
>
>

Views don't return any data. Only the queries issued against a view can
return data to the client. That means either queries in a stored
procedure or in a client application. So those queries would still have
to be the place for your "ORDER BY _ORDER_OF_A_" clause.

There is a problem. The predefined view order can only work if the
column(s) referenced by _ORDER_OF_A_ are also referenced in the SELECT
list of the *query* (not just the view). Otherwise it may not be
possible to determine the correct order and perform the sort. For this
reason, standard SQL (and other SQLs too in many cases) will raise an
error if the ORDER BY columns aren't also included in the SELECT list.
The implication is that the person writing the _ORDER_OF_A _ clause in
the query or proc must A) know which columns determine the order, and
B) include those columns in the SELECT list. So given those constraints
why wouldn't he or she just type the column names in the ORDER BY list
as well?

Remember also that SELECT * is very bad practice and isn't generally
used at all in production code so SELECT * is no good excuse for not
typing an ORDER BY clause. If your syntax is based on the assumption
that SELECT * will be used then it isn't likely to gain wide support
because most developers wouldn't be able or willing to take advantage
of it.

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