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 PaulVPOP3 - Internet Email Server/Gateway [EMAIL PROTECTED] http://www.pscs.co.uk/
RE: [sqlite] Insert all rows from old table into new table but in sorted order
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. -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 PaulVPOP3 - Internet Email Server/Gateway [EMAIL PROTECTED] http://www.pscs.co.uk/
Re: [sqlite] Insert all rows from old table into new table but in sorted order
You can't do that. The 'Insert' may (I'm not sure..) insert the data into 'NEWTABLE' in the 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. As the original responder said You can't do that. The records may or may not be inserted into the target table in the physical order you specified on the insert query. The physical order in the target table depends on how the SQL engine decides to write them. The order in which rows are returned from a query that doesn't specify order is undefined, meaning they may be in the order they are in the table, or some other order that is the result of the SQL engine's optimization. In other SQL engine's, you can force the physical order of the rows by using what MS SQL Server calls a clustered index, which isn't really an index at all, but rather a physical ordering of the rows in a table. I haven't seen anything about SQLite supporting clustered indexes, but you might check the www.sqlite.org website to see if it does or if there are any plans to include it in the future.
Re: [sqlite] Insert all rows from old table into new table but in sorted order
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.
RE: [sqlite] Insert all rows from old table into new table but in sorted order
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 PaulVPOP3 - Internet Email Server/Gateway [EMAIL PROTECTED] http://www.pscs.co.uk/
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 PaulVPOP3 - Internet Email Server/Gateway [EMAIL PROTECTED] http://www.pscs.co.uk/ -- Puneet Kishor
RE: [sqlite] Insert all rows from old table into new table but in sorted order
Seems to be top of my head, Is there any simple and sweet solution ? -Original Message- From: Brad DerManouelian [mailto:[EMAIL PROTECTED] Sent: Thursday, June 30, 2005 6:36 PM To: sqlite-users@sqlite.org; [EMAIL PROTECTED] Subject: RE: [sqlite] Insert all rows from old table into new table but in sorted order 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 PaulVPOP3 - Internet Email Server/Gateway [EMAIL PROTECTED] http://www.pscs.co.uk/
RE: [sqlite] Insert all rows from old table into new table but in sorted order
My apologies for being long-winded. Basically the answer is to not insert in a particular order and do your order by when you recall the data from NEWTABLE. Insert the data: Insert into NEWTABLE select * from OLDTABLE Then to get the data back in the order you want: select * from NEWTABLE order by no desc _brad -Original Message- From: Ajay [mailto:[EMAIL PROTECTED] Sent: Thursday, June 30, 2005 9:38 AM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Insert all rows from old table into new table but in sorted order Seems to be top of my head, Is there any simple and sweet solution ? -Original Message- From: Brad DerManouelian [mailto:[EMAIL PROTECTED] Sent: Thursday, June 30, 2005 6:36 PM To: sqlite-users@sqlite.org; [EMAIL PROTECTED] Subject: RE: [sqlite] Insert all rows from old table into new table but in sorted order 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 PaulVPOP3 - Internet Email Server/Gateway [EMAIL PROTECTED] http://www.pscs.co.uk/
RE: [sqlite] Insert all rows from old table into new table but in sorted order
Ok, it means that I can't do so ! -Original Message- From: Brad DerManouelian [mailto:[EMAIL PROTECTED] Sent: Thursday, June 30, 2005 7:33 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Insert all rows from old table into new table but in sorted order My apologies for being long-winded. Basically the answer is to not insert in a particular order and do your order by when you recall the data from NEWTABLE. Insert the data: Insert into NEWTABLE select * from OLDTABLE Then to get the data back in the order you want: select * from NEWTABLE order by no desc _brad -Original Message- From: Ajay [mailto:[EMAIL PROTECTED] Sent: Thursday, June 30, 2005 9:38 AM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Insert all rows from old table into new table but in sorted order Seems to be top of my head, Is there any simple and sweet solution ? -Original Message- From: Brad DerManouelian [mailto:[EMAIL PROTECTED] Sent: Thursday, June 30, 2005 6:36 PM To: sqlite-users@sqlite.org; [EMAIL PROTECTED] Subject: RE: [sqlite] Insert all rows from old table into new table but in sorted order 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 PaulVPOP3 - Internet Email Server/Gateway [EMAIL PROTECTED] http://www.pscs.co.uk/
RE: [sqlite] Insert all rows from old table into new table but in sorted order
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 PaulVPOP3 - Internet Email Server/Gateway [EMAIL PROTECTED] http://www.pscs.co.uk/ -- Puneet Kishor
Re: [sqlite] Insert all rows from old table into new table but in sorted order
On Jun 30, 2005, at 9:47 AM, Steve O'Hara wrote: 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. well, just for the sake of academics, I quote myself (original message below) -- 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. So, you are saying the same thing as I said. The db didn't really sort it any particular way. _You_ made the inverse insertion date to be the PK, and the db naturally returned it so. The PK could well have been something that might not sortable in the human-comprehensible way, such as a GUID. A reference was made to Perl hashes coming out randomly. Actually, internally (from what I understand), the hashes are also stored as arrays, and they do come out sorted a certain way. It is just that _that_ way might not be _the_ way we expect or can understand. Hence, the need, and therefore, the provision, for externally available SORTing methods... -Original Message- From: [EMAIL PROTECTED] [mailto:sqlite-users-return-6304-sohara=pivotal- [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
RE: [sqlite] Insert all rows from old table into new table but in sorted order
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 PaulVPOP3 - Internet Email Server/Gateway [EMAIL PROTECTED] http://www.pscs.co.uk/ PaulVPOP3 - Internet Email Server/Gateway [EMAIL PROTECTED] http://www.pscs.co.uk/
