To do with the same, what is wrong with this update SQL? update final2 set group_count = (select count(*) from final2 group by group_marker)
It makes group_count always 1, but should include higher values. The select by itself gives the right result. RBS On Wed, Oct 17, 2012 at 12:31 AM, Bart Smissaert <bart.smissa...@gmail.com> wrote: > Without the concatenation it runs fine, > enormously faster than with the concatenation. > Have checked and the result is fine as well. > Thanks again. > > 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
