Insert with order by

Hi all, In INFORMIX-SQL Version 7.32.FC2 I cannot run in dbaccess the following statement "insert into table_a select cust_code, co_code from strcustr ORDER BY CUST_CODE" (resulting in syntax error 201). But I can go around by "select cust_code, co_code from strcustr order by cust_code INTO TEMP tmp1 with no log" and then "insert into table_a select * from tmp1" to finally get sorted cust_codes in table_a. Is there any way to insert sorted cust_codes DIRECLTY from strcustr to table_a? Or SQL 7.32 is a bit obsolete? Thanks a lot Long N.

On 06/09/07, Long Nguyen <lnguyen@ruralco.com.au> wrote: > Hi all, > In INFORMIX-SQL Version 7.32.FC2 I cannot run in dbaccess the following > statement > "insert into table_a select cust_code, co_code from strcustr ORDER BY > CUST_CODE" (resulting in syntax error 201). > But I can go around by "select cust_code, co_code from strcustr order by > cust_code INTO TEMP tmp1 with no log" and then > "insert into table_a select * from tmp1" to finally get sorted cust_codes > in table_a. > Is there any way to insert sorted cust_codes DIRECLTY from strcustr to > table_a? Or SQL 7.32 is a bit obsolete? > > Thanks a lot > Long N. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > Long ISQL 7.32 is the latest version (albeit FC3). The issue really is why are you attempting to sort the records before inserting them into the permanent table? By it very nature the data stored in a relational database has no concept of order, this is only imposed when the data is extracted. If you place data into an empty table it will tend to be stored in the order you insert it, and 'may' be output in the same order if no ORDER BY clause is used. As soon as you have started to update and delete records from this table any further inserts will be placed in any 'gaps' left by the deleted records thus the stored data is out of order. Just run your INSERT INTO ..... SELECT ..... FROM .... with no ORDER BY. It will do the job with no need for the over head of the (useless) intermediate step. Keith

If there is a good reason to have the table's contents sorted, say you frequently retrieve 80-100% of the rows ORDER BY those columns, then after loading the table ALTER an index on that key TO CLUSTER or create a CLUSTER index on that key. That will sort the physical rows in the key order on disk to speed sequential access with a matching ORDER BY clause while the index will be used to guarantee that new rows are still returned in proper order even though they will not be physically ordered. Art S. Kagel ----- Original Message ----- From: Long Nguyen <ids@iiug.org> To: ids@iiug.org At: 9/05 22:37:40 Hi all, In INFORMIX-SQL Version 7.32.FC2 I cannot run in dbaccess the following statement "insert into table_a select cust_code, co_code from strcustr ORDER BY CUST_CODE" (resulting in syntax error 201). But I can go around by "select cust_code, co_code from strcustr order by cust_code INTO TEMP tmp1 with no log" and then "insert into table_a select * from tmp1" to finally get sorted cust_codes in table_a. Is there any way to insert sorted cust_codes DIRECLTY from strcustr to table_a? Or SQL 7.32 is a bit obsolete? Thanks a lot Long N. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.