The solution is to always specify an order when you want to return data in a particular order. SQL standard does not specify that rows come back in a particular order unless you specify. Order is never assumed as to enhance speed - especially for functions where order is irrelevant like totaling columns.
If you're familiar with how hashes are stored in C++ or Perl (and others I'm not familiar with), it's the same situation. If you don't pass it through a sort function, the interpreter decides how to return the values in the most efficient way which may not be the way they were originally inserted. _brad -----Original Message----- From: Ajay [mailto:[EMAIL PROTECTED] Sent: Thursday, June 30, 2005 8:21 AM To: sqlite-users@sqlite.org; [EMAIL PROTECTED] Subject: RE: [sqlite] Insert all rows from old table into new table but in sorted order Yaa that's what I wanted to do , So what do you think what could be the solution for this ? -----Original Message----- From: Steve O'Hara [mailto:[EMAIL PROTECTED] Sent: Thursday, June 30, 2005 5:28 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Insert all rows from old table into new table but in sorted order 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/
