Re: ORDER BY in VIEW not working

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

.

Relevant Pages

  • Re: ORDER BY in VIEW not working
    ... an ORDER BY clause returned ordered results. ... queries against the view or function that return data and those queries ... SQL Server 2000, determine what the row order might be and then ensure ... Whenever possible please post enough code to reproduce your problem. ...
    (comp.databases.ms-sqlserver)
  • Re: Not In and Not Exists
    ... you did not specify WHERE condition in the IN clause as you specify it in ... EXISTS clause and as result we got nothing. ... > SQL Server will try to optimize query plans so neither technique ought to ... > INSERT INTO Table2 VALUES ...
    (microsoft.public.sqlserver.programming)
  • Re: ORDER BY in VIEW not working
    ... a query to get an ordered data set, it's not okay to store that query as ... a view in a tool like SQL Server and see that ordered result in my ... That's why your expectations that queries against views ... If you want to propose a new feature that views should specify sorts ...
    (comp.databases.ms-sqlserver)
  • Re: store & retrieve files
    ... Sql2k's textcopy is a dblib app. ... Copies a single text or image value into or out of SQL Server. ... "where clause") of the specified 'table'. ... You will be prompted for any required options you did not specify. ...
    (microsoft.public.sqlserver.programming)
  • Re: Not In and Not Exists
    ... you did not specify WHERE condition in the IN clause as you specify it ... > EXISTS clause and as result we got nothing. ... >> SQL Server will try to optimize query plans so neither technique ought ... >> different query plans as a result. ...
    (microsoft.public.sqlserver.programming)