[sqlite] Rowid After Sorting
Dennis Cote wrote: >Then you should add an index on the Name column and use that to process >your queries in Name order more quickly. >create index on MyTable(Name); Thanks a lot Dennis. My process is more fast by means indexing. Dennis Cote wrote: >If you really insist on reordering your table, you must copy the data >somewhere else, empty the table, and reinsert the data in the order you >want the rowid to present. Note, this will not work if you plan on >inserting or deleting data after this initial insert. Ya. Right now each time when I insert or delete I will drop the tbl and insert once again like: create temp table t as select Id, Name from t order by Name; I think no other way than this will work out. Mahalakshmi.m wrote: > So, to find the index of a name, which is in sorted order, I need the >Rowid to be changed as shown in case 2 rather than in case 1. Dennis Cote wrote: >Why do you want to find the index of a Name? >Tables in SQL databases are not arrays. You don't use an index to >retrieve the data. Tables are more like unordered sets of data. Bcoz in my Application as input - I will give the starting letter say 'c' Then as output - I need the rowid of the name that is starting with 'c' if no name starts with that character then the rowid of the name which is next should be provided along with the name. For eg, I will create one temp tblb where I will store the name in sorted order itsef s follows: Rowid id name 1 4 aaa 2 2 bb 3 1 eee 4 3 zzz Input for my application is - c Required output is - rowid -3 and name - eee I will use the following querry: Select rowid,name from tbl where name >= 'c'; Think this gives a clear idea abt my requirement. Thanks & Regards, Mahalakshmi.M ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Rowid After Sorting
Dennis Cote wrote: >Then you should add an index on the Name column and use that to process >your queries in Name order more quickly. >create index on MyTable(Name); Thanks a lot Dennis. My process is more fast by means indexing. Dennis Cote wrote: >If you really insist on reordering your table, you must copy the data >somewhere else, empty the table, and reinsert the data in the order you >want the rowid to present. Note, this will not work if you plan on >inserting or deleting data after this initial insert. Ya. Right now each time when I insert or delete I will drop the tbl and insert once again like: create temp table t as select Id, Name from t order by Name; I think no other way than this will work out. Mahalakshmi.m wrote: > So, to find the index of a name, which is in sorted order, I need the >Rowid to be changed as shown in case 2 rather than in case 1. Dennis Cote wrote: >Why do you want to find the index of a Name? >Tables in SQL databases are not arrays. You don't use an index to >retrieve the data. Tables are more like unordered sets of data. Bcoz in my Application as input - I will give the starting letter say 'c' Then as output - I need the rowid of the name that is starting with 'c' if no name starts with that character then the rowid of the name which is next should be provided along with the name. For eg, I will create one temp tblb where I will store the name in sorted order itsef s follows: Rowid id name 1 4 aaa 2 2 bb 3 1 eee 4 3 zzz Input for my application is - c Required output is - rowid -3 and name - eee I will use the following querry: Select rowid,name from tbl where name >= 'c'; Think this gives a clear idea abt my requirement. Thanks & Regards, Mahalakshmi.M ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Rowid After Sorting
Mahalakshmi.m wrote: > > I wish to perform all operations in my code with sorted order of the Name > field. > Then you should add an index on the Name column and use that to process your queries in Name order more quickly. create index on MyTable(Name); If you really insist on reordering your table, you must copy the data somewhere else, empty the table, and reinsert the data in the order you want the rowid to present. Note, this will not work if you plan on inserting or deleting data after this initial insert. Adding new rows will place then at the end and assign a large rowid, regardless of the Name. create temp table t as select Id, Name from MyTable; delete from MyTable; insert into MyTable select Id, Name from t order by Name; > So, to find the index of a name, which is in sorted order, I need the Rowid > to be changed as shown in case 2 rather than in case 1. > Why do you want to find the index of a Name? Tables in SQL databases are not arrays. You don't use an index to retrieve the data. Tables are more like unordered sets of data. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Rowid After Sorting
1) SELECT rowid,Id,Name FROM MyTable ORDER BY Name; Rowid Id Name 4 4 aaa 3 3 bbb 2 2 xxx 1 1 zzz 2) "create table Temp as select Name from Mytable order by Name;" RowidId Name 1 4 aaa 2 3 bbb 3 2 xxx 4 1 zzz I wish to perform all operations in my code with sorted order of the Name field. So, to find the index of a name, which is in sorted order, I need the Rowid to be changed as shown in case 2 rather than in case 1. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Rowid After Sorting
> >But I need my rowid to be chaged as follows. > >Rowid Id Name >1 4 aaa >2 3 bbb >3 2 xxx >4 1 zzz You can't. Rowid isn't an index of where the row appeared in the results, it's a 'hidden' field in each row in the table. It just 'happens' that it's sequential by the order that rows were written to the table. If you think of it as just being like any other field in the data, then it'll all make sense. I suspect you're trying to use it for something it's not suitable for. The only thing you should really use it for (IMHO) is as a unique row identifier (hence the name). Some databases use a row 'GUID' or 'OID' instead, but they're essentially the same. Also, note that if you deleted the 'bbb' row from the table (for example), the results would come back as 14 aaa 32 xxx 4 1 zzz So, rowid '2' would be missing. PaulVPOP3 - Internet Email Server/Gateway [EMAIL PROTECTED] http://www.pscs.co.uk/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Rowid After Sorting
Why do you need your rowid to be changed? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Mahalakshmi.m Sent: Friday, March 14, 2008 1:03 AM To: sqlite-users@sqlite.org Subject: [sqlite] Rowid After Sorting Hi, I am working in 3.3.6 and my table looks like. Id - Integer Primary Key Name- Text Id Name 1 zzz 2 xxx 3 bbb 4 aaa SELECT rowid,Id,Name FROM MyTable ORDER BY Name; Rowid Id Name 4 4 aaa 3 3 bbb 2 2 xxx 1 1 zzz But I need my rowid to be chaged as follows. Rowid Id Name 1 4 aaa 2 3 bbb 3 2 xxx 4 1 zzz I tried with Views but its rowid is not changed. But by creating one new table like "create table Temp as select Name from Mytable order by Name;" gives the desired result as above. Its taking more time for this. So I there any other way I can do the same without creating table because in My table I am having many fields and each time I will create and drop the table for each fields. Can anyone please help to solve this. Thanks & Regards, Mahalakshmi.M ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Rowid After Sorting
The rowid is the b-tree key for the row. You cannot change it. Mahalakshmi.m wrote: > Hi, > > I am working in 3.3.6 and my table looks like. > > > > Id - Integer Primary Key > > Name- Text > > > > Id Name > > 1 zzz > > 2 xxx > > 3 bbb > > 4 aaa > > > > SELECT rowid,Id,Name FROM MyTable ORDER BY Name; > > > > Rowid Id Name > > > > 4 4 aaa > > 3 3 bbb > > 2 2 xxx > > 1 1 zzz > > > > But I need my rowid to be chaged as follows. > > > > Rowid Id Name > > > > 1 4 aaa > > 2 3 bbb > > 3 2 xxx > > 4 1 zzz > > > > I tried with Views but its rowid is not changed. > > > > But by creating one new table like > > "create table Temp as select Name from Mytable order by Name;" > > gives the desired result as above. > > > > Its taking more time for this. > > So I there any other way I can do the same without creating table because in > My table I am having many > > fields and each time I will create and drop the table for each fields. > > > > Can anyone please help to solve this. > > > > Thanks & Regards, > > Mahalakshmi.M > > > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Rowid After Sorting
> But I need my rowid to be chaged as follows. > > > > Rowid Id Name > > > > 1 4 aaa > > 2 3 bbb > > 3 2 xxx > > 4 1 zzz If you are looking Rowid to contain serial numbers every time then you can query for only Id and Name and then while extracting the query result you can append your own serial numbers as part of post processing the query result. HTH Bharath On 3/14/08 11:32 AM, "Mahalakshmi.m" <[EMAIL PROTECTED]> wrote: > > > Rowid Id Name > > > > 4 4 aaa > > 3 3 bbb > > 2 2 xxx > > 1 1 zzz > > > > But I need my rowid to be chaged as follows. > > > > Rowid Id Name > > > > 1 4 aaa > > 2 3 bbb > > 3 2 xxx > > 4 1 zzz > > > > I tried with Views but its rowid is not changed. > > > > But by creating one new table like > > "create table Temp as select Name from Mytable order by Name;" > > gives the desired result as above. > > > > Its taking more time for this. > > So I there any other way I can do the same without creating table because in > My table I am having many > > fields and each time I will create and drop the table for each fields. > > > > Can anyone please help to solve this. > > > > Thanks & Regards, > > Mahalakshmi.M --- Robosoft Technologies - Come home to Technology Disclaimer: This email may contain confidential material. If you were not an intended recipient, please notify the sender and delete all copies. Emails to and from our network may be logged and monitored. This email and its attachments are scanned for virus by our scanners and are believed to be safe. However, no warranty is given that this email is free of malicious content or virus. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Rowid After Sorting
Hi, I am working in 3.3.6 and my table looks like. Id - Integer Primary Key Name- Text Id Name 1 zzz 2 xxx 3 bbb 4 aaa SELECT rowid,Id,Name FROM MyTable ORDER BY Name; Rowid Id Name 4 4 aaa 3 3 bbb 2 2 xxx 1 1 zzz But I need my rowid to be chaged as follows. Rowid Id Name 1 4 aaa 2 3 bbb 3 2 xxx 4 1 zzz I tried with Views but its rowid is not changed. But by creating one new table like "create table Temp as select Name from Mytable order by Name;" gives the desired result as above. Its taking more time for this. So I there any other way I can do the same without creating table because in My table I am having many fields and each time I will create and drop the table for each fields. Can anyone please help to solve this. Thanks & Regards, Mahalakshmi.M ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
