Ok...how about with triggers then? This will give a unique number to each sequence as you insert them.
CREATE TABLE Test(ID,Value,Group_Marker); CREATE TRIGGER insert_trigger1 after insert on Test WHEN new.id=1 BEGIN UPDATE Test set Group_Marker=1; END; CREATE TRIGGER insert_trigger2 after insert on Test WHEN new.id > 1 and ((SELECT Group_Marker from Test where id=new.id-1 and Value=new.Value) IS NOT NULL) BEGIN UPDATE Test set Group_Marker=(select Group_Marker from Test where id=new.id-1) where id=new.id; END; CREATE TRIGGER insert_trigger3 after insert on Test WHEN new.id > 1 and ((SELECT Group_Marker from Test where id=new.id-1 and Value!=new.Value) IS NOT NULL) BEGIN UPDATE Test set Group_Marker=(select Group_Marker+1 from Test where id=new.id-1) where id=new.id; END; 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); INSERT INTO "Test" VALUES(7,'X',0); SELECT * FROM Test; You'll see that # 7 gets a new Group_Marker instead of repeating group#2. 1|D|1 2|X|2 3|X|2 4|X|2 5|A|3 6|B|4 7|X|5 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 5:45 PM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] find sequential groups Thanks, will try that. Yes, the ID field is an integer primary key autoincrement. Still running the old sql with concatenation. Looks I may need to kill that. RBS On Tue, Oct 16, 2012 at 11:38 PM, Igor Tandetnik <itandet...@mvps.org> wrote: > On 10/16/2012 6:29 PM, Bart Smissaert wrote: >> >> Actually, it really is slow, made worse by the fact that there is not >> one grouping >> field (value in my example), but three. I am running your SQL now, >> concatenating >> these 3 fields, but still running and looks will be a long time. >> Will have to improve it with indexes and maybe avoiding the concatenation. > > > This would avoid concatenation: > > > update MyTable set Group_Marker = ( > select count(*) from MyTable t1 > where t1.ID <= MyTable.ID and not ( > select (t2.Value1=t1.Value1 and t2.Value2=t1.Value2 and > t2.Value3=t1.Value3) > > from MyTable t2 where t2.ID < t1.ID > order by t2.ID desc limit 1 > ) > ); > > The only index that would be helful is one on ID, which I suspect you might > already have. > > -- > Igor Tandetnik > > _______________________________________________ > 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
