Op 26 jun 2014, om 10:32 heeft Rob Golsteijn het volgende geschreven:
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
I had the same idea, only using a view:
CREATE TABLE aaa (i);
INSERT INTO "aaa" VALUES(10);
INSERT INTO "aaa" VALUES(20);
INSERT INTO "aaa" VALUES(50);
INSERT INTO "aaa" VALUES(30);
INSERT INTO "aaa" VALUES(20);
CREATE VIEW vaaa AS
SELECT aaa.i,
(SELECT count() FROM aaa smaller where smaller.rowid <=
aaa.rowid) AS seqnr
FROM aaa;
select * from vaaa;
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
