At 12:52 30/06/2005, you wrote:
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.
But why?
It doesn't matter what order the rows are stored in the table. What matters
is what order you get them from the table. That's why you do the sorting
when you do the query, whenever and whatever the query is.
There may be implementation dependent ways to do what you want (eg Richard
says that what you're doing should work in current versions of SQLite), but
also, these ARE implementation dependent, so, if the underlying engine
changes (eg you use a newer version of SQLite, or you switch to MySQL or
something), it'll all fall over in unpredictable ways if you depend on this
implementation dependent behaviour.
Good programming practice dictates that you DON'T rely on implementation
dependent behaviour. If SQLite had an *explicit* way of requesting that
'order by'd inserts are honoured, and that an unordered query returns by
rowid, and that the rowid can never overflow (like other DBs have clustered
indices which are an explicit mechanism), then you may be able to do it, as
it would fail in a definite way if you tried to use this implementation
dependent behaviour when it wasn't available. But relying on implicit
implementation dependent behaviour (eg like expecting a perl hash to be
interated through in alphabetic order) is asking for trouble down the line,
and shouldn't get through any internal code reviews. (IMHO)
SQLite can handle sorting on an index very quickly. So, if you'll often
want to sort by 'no', just make an index on the 'no' column, and do 'order
by no desc' in all your queries requiring that ordering. You'll be glad you
did it that way in the future!
-----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/
Paul VPOP3 - Internet Email Server/Gateway
[EMAIL PROTECTED] http://www.pscs.co.uk/
