Some databases do actually allow you to maintain an insertion order. They do this for performance reasons so that the high cost of sorting is avoided - we have a few newspaper databases (>30 million full text stories) that have their primary key defined as the inverse story insertion date - this means that when a journalist searches for a story, they always get the results in 'latest first' order, which is nearly always what they want.
Typically, a journalist will run searches that return 10's of thousands of results, multiply that by the number of users within the paper (could be hundreds worldwide) and you can begin to see why this 'pre-sorting' of results is very useful. It's a little acedemic to say that a database should not store its data in a particular way, in a similar way to saying that all data MUST be normalised. In the real world, where performance is maybe more important than storage space, imposing a scheme on the data can be very important. Steve -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] rg]On Behalf Of Puneet Kishor Sent: 30 June 2005 14:09 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Insert all rows from old table into new table but in sorted order On Jun 30, 2005, at 7:21 AM, Ajay wrote: > > Yaa that's what I wanted to do , So what do you think what could be the > solution for this ? well, as others have suggested, there is no solution for it. Or, at least no solution that you should bother with. The purpose of the database is not to store the data in some particular view (order, collation, grouping, etc.) that you might want to view it later in. That is the reason the db provides methods to generate the views the way you want them. The only imposition is that the db might sort them internally by the PK, but that is also irrelevant -- the PK may not always be numerical (as in the case of a GUID), hence, you could get any old thing. In short, don't bother trying to insert the data in a particular order, because that is not what the db is designed for. Once you have the data in the db, then use the power of the db to morph the data into whatever views your heart desires. > > > -----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:sqlite-users-return-6291-sohara=pivotal- > [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/ > > > > > > > > -- Puneet Kishor
