I am storing time series data arriving from a sensor into (time,value) records, like so: 10:32 12 10:35 15 10:37 15 10:39 13 10:43 13 10:46 18
and I want to avoid storing repetitive data, so that the database should contain 10:32 12 10:35 15 10:39 13 10:46 18 where only the earliest time with the unchanging value is stored. I don't see how INSERT could be conditional on e.g. value != (select value from tbl order by time descending limit 1), so I thought I'd use triggers. The only way I could think of was to delete the new duplicate record after it has been inserted: create trigger cull after insert on tbl when (select value-lead(value) over (order by time desc) from a limit 1) = 0 begin delete from a where time like new.time; end; Is there a simpler way? _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users