Re: order by view

"rkusenet" <rkusenet@sympatico.ca> 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?? As already pointed out, that's an Oracle extension to the standard. However, having considered the following: 1) in Oracle too, the ORDER BY is done at SELECT time so you have the cost for the ORDER BY even if you would like not to order 2) Oracle views based on ORDER BY are not updatable 3) which is the interaction if I want to order by another column? (I'd say that the view ORDER BY should be as if last specified, if the composition property is properly maintained select-order-by(view-order-by(table)) = (select-order-by,view-order-by)(table) but I'd test that before being sure) , I prefer Informix interpretation, I'm happy they're more standard compliant, and, as Oracle DBA, I'd avoid a view with an ORDER BY. The problem is that application developers are tempted to exploit non-standard, non-portable features... :-) Umberto Quaia