Do this work for you? CREATE TABLE Test(ID,Value,Group_Marker); INSERT INTO "Test" VALUES(1,'D',0); INSERT INTO "Test" VALUES(2,'X',0); INSERT INTO "Test" VALUES(3,'X',0); INSERT INTO "Test" VALUES(4,'X',0); INSERT INTO "Test" VALUES(5,'A',0); INSERT INTO "Test" VALUES(6,'B',0); SELECT * FROM Test; CREATE TABLE Groups (Value); insert into Groups select distinct(Value) from test; SELECT * FROM Groups; update test set Group_Marker=(select rowid from Groups where Groups.Value=test.Value); SELECT * FROM Test;
sqlite> CREATE TABLE Test(ID,Value,Group_Marker); sqlite> INSERT INTO "Test" VALUES(1,'D',0); sqlite> INSERT INTO "Test" VALUES(2,'X',0); sqlite> INSERT INTO "Test" VALUES(3,'X',0); sqlite> INSERT INTO "Test" VALUES(4,'X',0); sqlite> INSERT INTO "Test" VALUES(5,'A',0); sqlite> INSERT INTO "Test" VALUES(6,'B',0); sqlite> SELECT * FROM Test; 1|D|0 2|X|0 3|X|0 4|X|0 5|A|0 6|B|0 sqlite> CREATE TABLE Groups (Value); sqlite> insert into Groups select distinct(Value) from test; sqlite> SELECT * FROM Groups; D X A B sqlite> update test set Group_Marker=(select rowid from Groups where Groups.Val e=test.Value); sqlite> SELECT * FROM Test; 1|D|1 2|X|2 3|X|2 4|X|2 5|A|3 6|B|4 Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems ________________________________________ From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Bart Smissaert [bart.smissa...@gmail.com] Sent: Tuesday, October 16, 2012 3:56 PM To: General Discussion of SQLite Database Subject: EXT :[sqlite] find sequential groups Trying to make a query that can mark records, indicating them to belong to a sequential group. Giving the most simple example: ID Value Group_Marker --------------------------------------- 1 D 1 2 X 2 3 X 2 4 X 2 5 A 3 6 B 4 Given I have a table with data in the fields ID and Value, but not in Group_Marker, can I make a SQL that will find the values in the field Group_Marker as above and update that field to hold those values. The field Value holds the data indicating a sequential group, so record 2, 3 and 4 are the second group, hence I need the 2 in the field Group_Marker. ID is the field indicating the sequence. This is easy to do in code with a simple loop, but not sure now how to do it in SQL. RBS _______________________________________________ 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
