Thanks, will give that a try as well. RBS
On Wed, Oct 17, 2012 at 12:00 AM, Black, Michael (IS) <michael.bla...@ngc.com> wrote: > 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
