Re: Order of rows in temp tables

Martin Brassard wrote: [SNIP] > My question is : Can we assume that the rows of the temp table will > always be returned in the physical order of the table if we don`t > spedify an order by clause in our query. > > What makes me hesitate to implement this is that I think that the clause > "order by" with "into temp" was a invalid syntax in earlier version of > Informix. Many others replied : I just tried it and the data from the temp table (1660 rows) was sorted in batches. Also in 7.30+ temp tables are created as fragmented tables across all tempspaces and loaded round robin so the return order would depend on several factors including PDQPRIORITY. It is best to include an ORDER BY clause WHEN EVER you want data in a particular order. =================================== I have also tried it with a large table on 7.30 and with different fields in the order by clause. The result is it gives the output from the temp table with the correct order. I have also tried to see the time it takes with 1. giving order by clause in select ...insert into temp and 2. selecting from temp table with order clause The result is that the time taken is almost the same. (the difference could be due to the other processes at different times) My question is : NOW IF THE ORDER IS CORRECT then why not do an order by once at the time of creation of temp table, and use that order whenever you access the temp table. If it gives you the results in the correct order and if you are going to use this temp table for many times, Then I would like to differ from others and suggest you use the order by at the time of creation of table and then just use the temp table without order by. Logic Works every time. Sunil Thakkar. If you are accessing the temp table in that order many times and if ______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com