On Thu, 07 Dec 2017 19:46:21 +0100, nitpi...@arcor.de wrote: > Hi all, > > I have a DB i.e. like this: > > table values > bc temp > 35 123 > 35 124 > 35 123 > 20 123 > 12 123 > 12 123 > 16 123 > 35 123 > 35 123 > 35 123 > 35 123 > 35 123 > > The value in temp (or all the other columns) is not of interest. > > Now I a looking for the basic concept how to count the number of rows where > bc=bc_of_last_row after the last different bc. > > SELECT COUNT(bc) FROM values WHERE bc=35; > > gives me the number of all rows with bc=35, so here 8. > But I am looking for 5, the number of rows after the last change of bc.
I recently had a similar but different requirement: Store a series of CPU temperatures over time, but if the temperature stays the same, just increment a count. Again, not the same as your problem, but my solution might inspire you with yours. CREATE TABLE cputemp ( epoch INTEGER PRIMARY KEY NOT NULL -- "epochfr" , epochto INTEGER DEFAULT NULL , ctemp INTEGER NOT NULL ); CREATE TRIGGER before_ins_cputemp BEFORE INSERT ON cputemp FOR EACH ROW WHEN NEW.ctemp == (SELECT ctemp FROM cputemp WHERE epoch == (SELECT max(epoch) FROM cputemp)) BEGIN UPDATE cputemp set epochto = NEW.epoch WHERE epoch == (SELECT max(epoch) FROM cputemp); SELECT RAISE(IGNORE); -- do not insert a new row END; -- Optional trigger: set epochto in new rows, -- could be done in other ways. CREATE TRIGGER after_ins_cputemp AFTER INSERT ON cputemp FOR EACH ROW WHEN NEW.epochto IS NULL BEGIN UPDATE cputemp SET epochto = NEW.epoch WHERE epoch == NEW.epoch; END; The INSERT looks like (awk code): printf "INSERT INTO cputemp (epoch,ctemp) " \ "VALUES ( 0 + strftime('%%s','now'),%s);\n",cputemp This can also be done in other ways of course. Hope this helps. -- Regards, Kees Nuyt _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users