I might be wrong, but if you don't specify a sort column, you will get the rows out in PRIMARY KEY order, irrespective of how you loaded the data. Therefore, you will need to do something a little more interesting with your loading statement to perhaps exclude the primary key and let the insert re-generate them.
Just a thought. Steve -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] rg]On Behalf Of Paul Smith Sent: 30 June 2005 12:23 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/