Thanks for the information. In my case I know that the rows will never be deleted so logically I could use max(rowid), but I am going to stick to my original solution of using a table to get the max id. But there is no in built table to maintain this, I will have to create a new table and maintain this information myself?
This resolves my problem. Thanks, Alok Please follow the attached hyperlink to an important disclaimer http://www.credit-suisse.com/asiapac/legal/securities/ -----Original Message----- From: Chris Campbell [mailto:ccampb...@mango-solutions.com] Sent: Wednesday, February 22, 2012 9:08 PM To: Jadhav, Alok; r-help@r-project.org Cc: Rory Arneil; Jakub Barszczewski Subject: RE: [R] sqlite create new unique id Hi Alok Yes, last_insert_rowid() will always look for the last row inserted in this connection, so you will always get 0 before inserting a new row in a new connection. Maintaining the connection may cause other problems as the last inserted rowid might not be relevant to your query. Getting max(rowid) does not work because it does not take deleted rows into account; you will want to avoid inserting a row with rowid that was previously in the database. Sqlite performs autoincrement in a similar manner to your solution by using another table to store the largest rowid, and increments the number each time you insert a new row. Since you have text you will need to manually increment the value each time you insert a new row. You should be able to automate this step in a satisfactory way by building the new rowid for the table based on the expected structure of your field values. Best wishes Chris Chris Campbell MANGO SOLUTIONS Data Analysis that Delivers +44 1249 767700 -----Original Message----- From: Jadhav, Alok [mailto:alok.jad...@credit-suisse.com] Sent: 22 February 2012 01:30 To: Chris Campbell; r-help@r-project.org Cc: Rory Arneil; Jakub Barszczewski Subject: RE: [R] sqlite create new unique id Hi Chris, Apologies for getting back late. In my case I am creating a new connection everytime I insert a new trade. I thought last_insert_rowid() would insert max(rowid) for given table? I can try to insert bunch of rows in a data frame, but whenever I create a new connection, I will get last_insert_rowid() as 0? Then in this case it wont be a unique number. I thought last_insert_rowid() is used to get the unique number for given table (similar to AUTOINCREMENT feature). If my understanding is incorrect then what is the right way to gereate a unique number for given table? As I mentioned in my first post, I don't want to use INTEGER PRIMARY KEY because it has to be a text. For now, I have another table where I stored last max number and whenever I insert new transaction I get max number from this table. This approach works but there has to be a better way to do this. Regards, Alok Please follow the attached hyperlink to an important disclaimer http://www.credit-suisse.com/asiapac/legal/securities/ -----Original Message----- From: Chris Campbell [mailto:ccampb...@mango-solutions.com] Sent: Tuesday, February 21, 2012 6:14 PM To: Jadhav, Alok; r-help@r-project.org Cc: Rory Arneil; Jakub Barszczewski Subject: RE: [R] sqlite create new unique id Hi Alok Are you certain that you are keeping your connection open? last_insert_rowid() returns id of last inserted row from the current database connection (which invoked the function). Is it possible that every time you run the query you making a new connection? Hope this helps, Chris Campbell MANGO SOLUTIONS Data Analysis that Delivers +44 1249 767700 -----Original Message----- From: r-help-boun...@r-project.org [mailto:r-help-boun...@r-project.org] On Behalf Of Alok Jadhav Sent: 21 February 2012 06:59 To: r-help@r-project.org Subject: [R] sqlite create new unique id Hi everyone, I am trying to insert a row in sqlite table with my own unique id. I want to create unique id using sqlite internal function last_insert_rowid() which returns the next max rowid of the table which is always unique. I tested this using sqlite and it works fine but when i run the same query using RSQlite from r prompt, my query doesn't create new unique id. last_insert_rowid() always returns 0 value from RSQLite always returns 0. How can I get a unique id using RSQLite ? my query looks like this "insert into PrimaryIdTable values ('AMP AT Equity','bbg','2001-01-01','2099-01-01','P'||last_insert_rowid())" reason i am not using INTEGER PRIMARY KEY is because i want to keep my key as TEXT datatype. This is because another table has same field for secondary id and both these keys appear in same table so i want to call them primary key (P1, P2 ) or secondary key (S1, S2 etc) . Thanks for your help, Alok -- View this message in context: http://r.789695.n4.nabble.com/sqlite-create-new-unique-id-tp4406114p4406 114.html Sent from the R help mailing list archive at Nabble.com. ______________________________________________ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code. LEGAL NOTICE This message is intended for the use of the named recipient(s) only and may contain confidential and / or privileged information. If you are not the intended recipient, please contact the sender and delete this message. Any unauthorised use of the information contained in this message is prohibited. Mango Business Solutions Limited is registered in England under No. 4560258 with its registered office at Suite 3, Middlesex House, Rutherford Close, Stevenage, Herts, SG1 2EF, UK. PLEASE CONSIDER THE ENVIRONMENT BEFORE PRINTING THIS EMAIL ======================================================================== ======= Please access the attached hyperlink for an important electronic communications disclaimer: http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html ======================================================================== ======= LEGAL NOTICE This message is intended for the use of the named recipient(s) only and may contain confidential and / or privileged information. If you are not the intended recipient, please contact the sender and delete this message. Any unauthorised use of the information contained in this message is prohibited. Mango Business Solutions Limited is registered in England under No. 4560258 with its registered office at Suite 3, Middlesex House, Rutherford Close, Stevenage, Herts, SG1 2EF, UK. PLEASE CONSIDER THE ENVIRONMENT BEFORE PRINTING THIS EMAIL =============================================================================== Please access the attached hyperlink for an important electronic communications disclaimer: http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html ______________________________________________ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
