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.
