Re: Order BY Cluase on View

--- Gorazd Hribar Rajteri' wrote: > YES - you can use order by in select clause on view. > You cannot use ORDER BY > clause in CREATE VIEW statement (prior to version > 9.40). Can we really do that!!! I am not up to date on 9.40 features, but I do remember a big discussion we had here a few days back and the consensus was it is npt supported on Informix and did not make and sense to have a order by in CREATE VIEW, suppose you create a view with order by clause on one column, and then you fire another query with order by on another column, what happens then? How would the user know that the table has been created with an order by inside. I really do not understand what value add would this feature provide. Regards, Asheesh. > > Gorazd > > "KchittiBhooma,NarsaReddy" <kchittin@ks2.stph.net> > wrote in message > news:bm5m4a$tdh$1@terabinaries.xmission.com... > > > > Hi > > > > I have question: > > Can I use Order By Clause on View? > > Ex: Table Name: XXX > > View: CREATE VIEW YYY "Select * from XXX" > > I want to use Order By on YYY > > Select * from YYY order by <<Column Name>>> > Does it work??? > > > > Thx > > In adv > > > > sending to informix-list > __________________________________ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com sending to informix-list

"Asheesh Rastogi" <asheesh_iiug@yahoo.com> wrote > Can we really do that!!! I am not up to date on 9.40 > features, but I do remember a big discussion we had > here a few days back and the consensus was it is npt > supported on Informix and did not make and sense to > have a order by in CREATE VIEW, suppose you create a > view with order by clause on one column, and then you > fire another query with order by on another column, > what happens then? How would the user know that the > table has been created with an order by inside. Oracle does it and I believe what it does is that it first orders it based on the VIEW definiton and then further orders it based on SQL given. So the end result will be the order given in the SQL, even though it is inefficient to do it twice. > I really do not understand what value add would this > feature provide. Don't say that. Sometime back I posted this here bcos we wanted that in our system so as not to change the application code. BTW SQL Server also does not support ORDER BY in view definition.

I wasn't sure for 9.40, but now I have checked - NO you cannot use ORDER BY clause in CREATE VIEW statements in 9.40 either. Taken from Guide to SQL: Syntax page 2-312 (http://publibfi.boulder.ibm.com/epubs/pdf/ct1sqna.pdf). Gorazd "Asheesh Rastogi" <asheesh_iiug@yahoo.com> wrote in message news:bm6qu0$7d6$1@terabinaries.xmission.com... > > > --- Gorazd Hribar Rajteri' wrote: > > YES - you can use order by in select clause on view. > > You cannot use ORDER BY > > clause in CREATE VIEW statement (prior to version > > 9.40). > Can we really do that!!! I am not up to date on 9.40 > features, but I do remember a big discussion we had > here a few days back and the consensus was it is npt > supported on Informix and did not make and sense to > have a order by in CREATE VIEW, suppose you create a > view with order by clause on one column, and then you > fire another query with order by on another column, > what happens then? How would the user know that the > table has been created with an order by inside. I > really do not understand what value add would this > feature provide. > > Regards, > Asheesh. > > > > Gorazd > > > > "KchittiBhooma,NarsaReddy" <kchittin@ks2.stph.net> > > wrote in message > > news:bm5m4a$tdh$1@terabinaries.xmission.com... > > > > > > Hi > > > > > > I have question: > > > Can I use Order By Clause on View? > > > Ex: Table Name: XXX > > > View: CREATE VIEW YYY "Select * from XXX" > > > I want to use Order By on YYY > > > Select * from YYY order by <<Column Name>>> > > Does it work??? > > > > > > Thx > > > In adv > > > > > > sending to informix-list > > > > > __________________________________ > Do you Yahoo!? > The New Yahoo! Shopping - with improved product search > http://shopping.yahoo.com > sending to informix-list

rkusenet wrote: > "Asheesh Rastogi" <asheesh_iiug@yahoo.com> wrote > > >>Can we really do that!!! I am not up to date on 9.40 >>features, but I do remember a big discussion we had >>here a few days back and the consensus was it is npt >>supported on Informix and did not make and sense to >>have a order by in CREATE VIEW, suppose you create a >>view with order by clause on one column, and then you >>fire another query with order by on another column, >>what happens then? How would the user know that the >>table has been created with an order by inside. > > > Oracle does it and I believe what it does is that it first > orders it based on the VIEW definiton and then further orders > it based on SQL given. So the end result will be the order > given in the SQL, even though it is inefficient to do it twice. > > >>I really do not understand what value add would this >>feature provide. > > > Don't say that. Sometime back I posted this here bcos we wanted > that in our system so as not to change the application code. > > BTW SQL Server also does not support ORDER BY in view definition. I'm hopping I'm repeating myself: I cannot understand the point of creating an ordered by view unlesse it's a materialized view (which does not exist in Informix). Even so I cannot understand it completely. It's common sense that the database doesn't retrieve the records in any particular order. If the application requires a specific order it must specify it through the ORDER BY clause. Any application that takes advantage of an ordered by VIEW is a very bad written application from my point of view. Finally remember that a view does not exist. Why order by something that does not exist? To say that an application takes advantage of an ordered by VIEW is the same as to assume we could have an ordered by table... Regards

Fernando Nunes wrote: > rkusenet wrote: > >> "Asheesh Rastogi" <asheesh_iiug@yahoo.com> wrote >> >> >>> Can we really do that!!! I am not up to date on 9.40 >>> features, but I do remember a big discussion we had >>> here a few days back and the consensus was it is npt >>> supported on Informix and did not make and sense to >>> have a order by in CREATE VIEW, suppose you create a >>> view with order by clause on one column, and then you >>> fire another query with order by on another column, >>> what happens then? How would the user know that the >>> table has been created with an order by inside. >> >> >> >> Oracle does it and I believe what it does is that it first >> orders it based on the VIEW definiton and then further orders >> it based on SQL given. So the end result will be the order >> given in the SQL, even though it is inefficient to do it twice. >> >> >>> I really do not understand what value add would this >>> feature provide. >> >> >> >> Don't say that. Sometime back I posted this here bcos we wanted >> that in our system so as not to change the application code. >> >> BTW SQL Server also does not support ORDER BY in view definition. > > > I'm hopping I'm repeating myself: > > I cannot understand the point of creating an ordered by view unlesse > it's a materialized view (which does not exist in Informix). > Even so I cannot understand it completely. > > It's common sense that the database doesn't retrieve the records in > any particular order. If the application requires a specific order it > must specify it through the ORDER BY clause. > > Any application that takes advantage of an ordered by VIEW is a very > bad written application from my point of view. > > Finally remember that a view does not exist. Why order by something > that does not exist? To say that an application takes advantage of an > ordered by VIEW is the same as to assume we could have an ordered by > table... > > Regards > In Oracle a view does have a physical existance. At least in the sense of a physical table sitting in the TEMP tablespace. The advantage of a view created with the ORDER BY clause is that if the view is used repeatedly by an application, say for example a view created by the joining of two or more tables and used for validation (lookup) it is better to pay the price of ordering the data once when the view is first loaded (first SELECT statement against it) than to take the repeated hits by ordering the data for every user that accesses the view every time they access it. ORDER BY, after all, has a very high performance penalty. -- Daniel Morgan http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp damorgan@x.washington.edu (replace 'x' with a 'u' to reply)

> > Any application that takes advantage of an ordered by VIEW is a very bad > written application from my point of view. > Order By support was added to the view definition in Oracle (and I believe in DB2 and the standard) to support the use of the order by clause in an inline view definition. Inline view definitions are used in the SQL OLAP capabilities to great effect - for instance something like SELECT SUBSTR(prod_category,1,8) AS CATEG, prod_subcategory, prod_id, SALES FROM (SELECT p.prod_category, p.prod_subcategory, p.prod_id, SUM(amount_sold) as SALES, SUM(SUM(amount_sold)) OVER (PARTITION BY p.prod_category) AS CAT_SALES, SUM(SUM(amount_sold)) OVER (PARTITION BY p.prod_subcategory) AS SUBCAT_SALES, RANK() OVER (PARTITION BY p.prod_subcategory ORDER BY SUM(amount_sold) ) AS RANK_IN_LINE FROM sales s, customers c, countries co, products p WHERE s.cust_id=c.cust_id AND c.country_id=co.country_id AND s.prod_id=p.prod_id AND s.time_id=to_DATE('11-OCT-2000') GROUP BY p.prod_category, p.prod_subcategory, p.prod_id ORDER BY prod_category, prod_subcategory) WHERE SUBCAT_SALES>0.2*CAT_SALES AND RANK_IN_LINE<=5; This, for instance, finds the 5 top-selling products for each product subcategory where that product contributes more than 20% of the sales within its product category. 'Bad written' - maybe. Powerful - very.