Thanks, very nice solution that! Yes, I realise that this is a lot faster in code, but for now that is no option. 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.
RBS On Tue, Oct 16, 2012 at 10:53 PM, Igor Tandetnik <itandet...@mvps.org> wrote: > On 10/16/2012 4:56 PM, Bart Smissaert wrote: >> >> 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. > > > Something like this - but note that it's mostly an academic exercise. A > simple loop would work orders of magnitude faster than this statement. > > update MyTable set Group_Marker = ( > select count(*) from MyTable t1 > where t1.ID <= MyTable.ID and t1.Value not in ( > select t2.Value from MyTable t2 where t2.ID < t1.ID > order by t2.ID desc limit 1 > ) > ); > > In prose, for each record count the number of records below it (inclusive) > that are first-in-group; where first-in-group in turn is defined as "a > record such that the next record down by ID has a different Value, or there > is no smaller ID at all". > -- > 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
