Rick Ratchford <r...@amazingaccuracy.com> wrote: > Is it possible, using SQL, to do comparisions across records? > > Suppose that you had 1 field called TAX and you wanted to compare > each one to the previous one.
What do you mean by "previous one"? Records in a table don't have any implicit ordering. Do you have some kind of a timestamp field that imposes the order? > Record 1 = TAX (45) > Record 2 = TAX (65) > Record 3 = TAX (22) > > So using the data above, I would want to compare Record 2 (65) to > Record 1 (45) and store it into a new field called DIRECTION. > > Record 1 = TAX (45) DIRECTION (Null) > Record 2 = TAX (65) DIRECTION (up) > Record 3 = TAX (22) DIRECTION (down) > > As each records TAX field is compared to the previous records TAX > field, if higher, then DIRECTION = up. If lower, DIRECTION = down. update taxTable set DIRECTION = (select case when taxTable.TAX < prevTax then 'down' when taxTable.TAX > prevTax then 'up' else null end from (select t2.TAX as prevTax from taxTable t2 where t2.Date < taxTable.Date order by Date desc limit 1)); Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
