On Thu, 2005-06-30 at 16:35 +0530, Ajay wrote: > 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 ??? >
The ORDER BY clause on a SELECT used to insert into a table has been honored since SQLite version 2.7.2 (Sep 2002). You must be using a really old version of SQLite. In SQLite, the INTEGER PRIMARY KEY (a.k.a. ROWID) acts as a cluster index. Any query on a table that does not use an index or an ORDER BY clause will output rows in ROWID order. This is not a guarantee, but it is how things work now. What is guaranteed is that rows are stored in a table in order of ascending ROWID. When new rows are added to a table, unless the ROWID overflows or is specified, each new row is added at the end. So if you do CREATE TABLE newtable AS SELECT * FROM oldtable ORDER BY x; SELECT * FROM newtable; The results will come out sorted by X. Again - this is not guaranteed but it is how things are currently implemented. It might change tomorrow and I would not consider that an incompatible change. If you want things in a specific order, then use an ORDER BY clause. But the behavior you seek is the current behavior.