order by view

It seems even in Informix 9.4 we can not use order by clause in a view create view viewnameas select a,b from table1 order by b ; will result in syntax error. SQl Server 2000 also does not allow this. However Oracle allows. We recenetly had to change the definition of a view and would have loved to have order by clause in the view definition. Now we are forced to change the application code for proper ordering. why is this restriction??

rkusenet wrote: > It seems even in Informix 9.4 we can not use order by clause > in a view > > create view viewname> as select a,b > from table1 > order by b ; > > will result in syntax error. > > SQl Server 2000 also does not allow this. However Oracle > allows. > > We recenetly had to change the definition of a view and would > have loved to have order by clause in the view definition. Now > we are forced to change the application code for proper ordering. > > why is this restriction?? > > > And what is the advantage of having an order by clause in a view? It's the same as assuming a "SELECT *" will return the result in some specific order. If you need and ordered result set, you should use an ORDER BY clause in the SELECT statement. regards.

Fernando Nunes wrote: > > And what is the advantage of having an order by clause in a view? > It's the same as assuming a "SELECT *" will return the result in some > specific order. > If you need and ordered result set, you should use an ORDER BY clause > in the SELECT statement. I agree. The orderby in Oracle is a port-specific corruption of the sql standards. Admittedly all engines have these so I'm not saying "nyarr-nyarr" at Oracle (at least, not for this issue :-) SQL selects are inherently unordered. The ORDER BY should be on the individual SELECT statement used in the application. If you care about portability then don't count on local optimisations that some engine offer. I presume, if you put an ORDER BY in an Oracle view, and then the same ORDER BY in the SELECT on that view, the engine will be smart enough not to twice-order it? If so, understand this as a local optimisation that Oracle offers but not something that should allow you to lazily leave the ORDER BY clause off individual selects. I seriously doubt other engines will rush in to implement this feature.

Andrew wrote: > I presume, if you put an ORDER BY in an Oracle view, and then the same ORDER > BY in the SELECT on that view, the engine will be smart enough not to > twice-order it? If so, understand this as a local optimisation that Oracle > offers but not something that should allow you to lazily leave the ORDER BY > clause off individual selects. Err... "twice re-order"?! Everybody seems to forget (or I'm terribly mistaken) that a View DOES NOT exist. It is just a "comodity". It is instantiated each time a select is done. Unless of course we're talking about materialized views. Regards.

"Fernando Nunes" <spam@domus.online.pt> wrote in message news:bhd7bq$10inrn$1@ID-161111.news.uni-berlin.de... > Andrew wrote: > > > I presume, if you put an ORDER BY in an Oracle view, and then the same ORDER > > BY in the SELECT on that view, the engine will be smart enough not to > > twice-order it? If so, understand this as a local optimisation that Oracle > > offers but not something that should allow you to lazily leave the ORDER BY > > clause off individual selects. > > Err... "twice re-order"?! > Everybody seems to forget (or I'm terribly mistaken) that a View DOES NOT exist. > It is just a "comodity". It is instantiated each time a select is done. I think Andrew has a point. if a view is declared as select a,b,c from table order by a it effectively means that the view can never be used for any other ordering. If an application wants to order it by b, they can't use this view. Now I see why Informix does not allow order by in a view. To put it simply, it is a bad design to allow ordering inside a view. rk-