Re: ORDER BY in VIEW not working

Serge Rielau wrote:
Here the user chose to further process the rows of the view in way that
cannot preserve order. That does not invalidate the usefulness of the
proposal IMHO.


So here's the problem. There are many, many examples using joins,
unions, aggregations and projections where ordering cannot sensibly be
permitted based on the underlying view(s). So do you want to detect
these at design time and raise an error or do you just give a warning
and allow creation of a query with the ORDER BY clause that won't in
fact be ordered?

If you allow the creation of queries and views that have these invalid
ORDER_OF_? clauses then what if the ORDER BY clause on the base view
changes? The person creating the view will presumably see a warning but
what about the users of queries against that view? They will see
nothing wrong. The users' queries will still have the same ORDER_OF_?
clauses and they will even appear to work normally. They may even
display the same ordering sometimes but then at other times they may
show a different order because in reality no logical ordering is
actually taking place. Now if the users are anything like RickW they
will be saying "My view has an ORDER BY but it isn't ordered. It used
to work in the old version. IBM stinks. They did this to me without any
advance warning."

I'd say the only reasonable solution is to validate the ORDER_OF_?
clause EVERY TIME a query is created or executed and to disallow the
query if the ordering is a non-permitted one. This would surely be
incredibly frustrating to the user unless he knows the column names
being used in the ordering or has access to that information. Only if
the user developing the query knows what the order is can he know
whether or not it is appropriate to use ORDER BY ORDER_OF_?.

So haven't we come full circle? Not only does the user have to know
which views contain ORDER BY clauses he also has to know what the order
is. If the user KNOWS what the ordering is and has to take that into
account when designing the query then what do we gain from the
ORDER_OF_? clause? Is this really only about saving a few keystrokes
and does that really justify the added complexity of validating views
and queries?

--
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: probably not as complex as Im thinking...
    ... No, sorry, I get a syntax error in Group By clause here, and I tried not ... >> OK, I have 4 queries, the SQL view of which I have below, with brief ... Please understand that I use the Query Design Grid to make ...
    (microsoft.public.access.queries)
  • Re: String manipulation
    ... So long as you'd need the WHERE clause to differentiate when a field should ... that you have cited will not lend itself to a single query. ... single record may not all meet the same WHERE constraint. ... Those queries won't just mean running ...
    (microsoft.public.access.queries)
  • Re: Foxpro sql limitations
    ... The limit of parameters in an IN clause is 24 not 20 I believe. ... limit is 255 chars in a quoted string so if your passing the a quoted string ... > responsibility to deal with as opposed to the DBMS query engine. ... > follow-up queries between the two connections. ...
    (microsoft.public.fox.vfp.queries-sql)
  • Re: Foxpro sql limitations
    ... > I am developing an ASP application that has to interface with a Foxpro ... > where you can't have more than 20 items in an IN clause. ... > responsibility to deal with as opposed to the DBMS query engine. ... > follow-up queries between the two connections. ...
    (microsoft.public.fox.vfp.queries-sql)
  • RE: Any good T-SQL quick reference recommended?
    ... The full syntax of the SELECT ... SELECT Clause ... Specifies the columns to be returned by the query. ... Specifies that duplicate rows can appear in the result set. ...
    (microsoft.public.sqlserver.programming)