> > You can't do that. > > The 'Insert' may (I'm not sure..) insert the data into 'NEWTABLE' in the
> 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. As the original responder said "You can't do that". The records may or may not be inserted into the target table in the physical order you specified on the insert query. The physical order in the target table depends on how the SQL engine decides to write them. The order in which rows are returned from a query that doesn't specify order is undefined, meaning they may be in the order they are in the table, or some other order that is the result of the SQL engine's optimization. In other SQL engine's, you can force the physical order of the rows by using what MS SQL Server calls a "clustered" index, which isn't really an index at all, but rather a physical ordering of the rows in a table. I haven't seen anything about SQLite supporting clustered indexes, but you might check the www.sqlite.org website to see if it does or if there are any plans to include it in the future.
