you misinterpreted my problem, I want to add all rows of old table into new table but with sorted order I don't want to fire another query (select * from newtable order by desc no ) to give sorted rows, I want to insert all rows in sorted order into new table.
-----Original Message----- From: Paul Smith [mailto:[EMAIL PROTECTED] Sent: Thursday, June 30, 2005 4:53 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Insert all rows from old table into new table but in sorted order >I can insert all rows of existing table into new table having same columns >using query : > >Insert into NEWTABLE select * from OLDTABLE > >But I want all rows of NEWTABLE sorted by field No, > >So I used query > >Insert into NEWTABLE select * from OLDTABLE order by no desc > >But it is not giving me sorted output as new table? > >Can you tell me where I am wrong ??? You can't do that. The 'Insert' may (I'm not sure..) insert the data into 'NEWTABLE' in the "right order" , but then, when you do an unordered query on 'NEWTABLE', the results are returned in an undefined order - not necessarily in the order they were inserted into the table You should do the sorting when you read 'NEWTABLE' So, instead of Insert into NEWTABLE select * from OLDTABLE order by no desc select * from NEWTABLE do Insert into NEWTABLE select * from OLDTABLE select * from NEWTABLE order by no desc Paul VPOP3 - Internet Email Server/Gateway [EMAIL PROTECTED] http://www.pscs.co.uk/