Related to this thread, I wonder if it's possible to create a view which can give me a value from the row immediately above. E.g. given the table:
unix_time val ----------+--- 1325376000|val1 1325376300|val2 1325376600|val3 1325376900|val4 (the first column is a unix timestamp and unique) can I create a view which gives me: unix_time val prev_unix_time ----------+----+---------- 1325376000|val1| 1325376300|val2|1325376000 1325376600|val3|1325376300 1325376900|val4|1325376600 Something like this will not work: create view new as select unix_time, val, (select unix_time from old where new.unix_time < old.unix_time order by unix_time desc limit 1) as prev_unix_time from old; as I can't refer to new.unix_time inside the view that defines "new". The idea is, if this is possible, then I should be able to get my weighted average by something like this (not verified, but you get the idea): select sum(val * (unix_time - prev_unix_time)) / sum(unix_time - prev_unix_time) from new; -- Steinar _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
