Hi Dave, You can of course also calculate a new sequence number based on the row ids. Just count the number of records with a smaller or equal rowid. This way it doesn't matter if rowid starts at 1 or if there are any gaps in the range.
Example: CREATE TABLE aaa (i, seqnr); INSERT INTO "aaa" VALUES(10,NULL); INSERT INTO "aaa" VALUES(20,NULL); INSERT INTO "aaa" VALUES(50,NULL); INSERT INTO "aaa" VALUES(30,NULL); INSERT INTO "aaa" VALUES(20,NULL); UPDATE aaa SET seqnr=(SELECT count() FROM aaa smaller where smaller.rowid <= aaa.rowid); select * from aaa; i|seqnr 10|1 20|2 50|3 30|4 20|5 Regards Rob Golsteijn > Hi all, > >I have some rows in a table (not very many, typically less than 20) and I >want to generate a unique, sequential number for each row. In another dbms >I've used a row_number function (amongst others) to achieve this but I can't >see anything with equivalent functionality in sqlite3. My apologies if I've >missed something. > > > >I thought about using the 'rowid' and in some simple testing that seems to >give me what I want. But I need to check a couple of things. > > > >1) Is there a function that will give me unique, sequential numbers? > > > >2) Assuming that my processing follows this pattern: empty table T1 >completely, insert a number of rows, insert/select from T1 into T2. On the >'select' processing will the 'rowid' >** always ** start at 1? > > > >3) If I repeat the processing pattern shown in #2 above, will >subsequent selects always have rowid that starts from 1? > > > >Yes, I know that I could select the rows back to my application, generate >the numbers and then insert rows back into the table but I'm trying to do >this within the dbms. > > > >All help or ideas gratefully received. > > > >Cheers, > >Dave _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
