Order in the Database

I have two (related?) questions about using "order by" with Informix (I'm using SE-7, the free Linux version). First my questions: Q1: "select x from tabname order by y" doesn't work. How can I make it work, or what's a good workaround? Q2: You can't create a view that has an 'order by' clause in it. Is there a workaround to this? Now my thoughts: I don't think Q1 is hard-- for example, Informix could simply do a "select x,y from tabname order by y" (which works fine), and then strip off the column y which I'm not using before returning the result to me. In other words, it shouldn't be hard to "hide" the ORDER BY column even if Informix needs it internally to do the query. Am I missing something? For Q2, I can sort of see the issue-- views are like tables, and tables don't have any implicit order (for one thing, if you could create a view with order, then "select * from view order by x" would be ambigious). So, I'm wondering if there's something SIMILAR to a view that does what I want? I'm not too familiar with terminology, but I think this would be a cross between a view and a form? Thoughts, suggestions, etc all welcomed! Thanks, Math Prof (mathprof@bigfoot.com) Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.

mathprof@bigfoot.com wrote: > > I have two (related?) questions about using "order by" with Informix > (I'm using SE-7, the free Linux version). First my questions: > > Q1: "select x from tabname order by y" doesn't work. How can I make it > work, or what's a good workaround? You've described the workaround. > Q2: You can't create a view that has an 'order by' clause in it. Is > there a workaround to this? > > Now my thoughts: > > I don't think Q1 is hard-- for example, Informix could simply do a > "select x,y from tabname order by y" (which works fine), and then > strip off the column y which I'm not using before returning the > result to me. In other words, it shouldn't be hard to "hide" the > ORDER BY column even if Informix needs it internally to do the > query. Am I missing something? ANSI SQL requires all columns in the ORDER BY list to appear in the SELECT list. Informix is one of the few holdouts not implementing the enhancement you suggest. If you omit the ordering column in the SELECT list, then you no longer have relational data; there isessential ordering information in the result set. This isn't always critical, but it is *a* reason for not having done it. More likely, though, it hasn't been perceived as a big issue because the workaround is so simple -- you simply select the sorting column too. > For Q2, I can sort of see the issue-- views are like tables, and > tables don't have any implicit order (for one thing, if you could > create a view with order, then "select * from view order by x" would > be ambigious). So, I'm wondering if there's something SIMILAR to a > view that does what I want? I'm not too familiar with terminology, > but I think this would be a cross between a view and a form? What is the benefit of a view with an ORDER BY? Since the database can return the data from a table in any order unless constrained by an ORDER BY clause, and a view is only a special case of a table, the only reason to try embedding an ORDER BY statement in the view rather than in the SELECT which queries the view is (perhaps) to save typing. Then again, if the user decided that instead of having the data in the order specified in the view statement, they wanted it in some completely different order, what should the server do? Both sorts? Ignore the sort on the VIEW? Presumably the latter, but... An ORDER BY clause can only appear when data is being fetched to the application -- in a cursor, in other words. Hence, it cannot appear in a VIEW. And again, you can appeal to the SQL standard if necessary. If I was designing a relational query engine, I'd be sorely tempted to have it return data in a pseudo-random order when not constrained by an ORDER BY clause. For example, it could establish 4 rows which will be returned and return a random one of these, and replace it with the next row which will be returned. That way, you don't get people relying on any implicit ordering of the data, even if the query is using a sequential scan in index order. If you want the order, say so, in other words. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h>