If you want to use the rowid to order the rows (or an auto-incrementing primary key field), you could do something like this:
update tst set Direction= (select case when b.tax < tst.tax then "Up" when b.tax>=tst.tax then "Down" else null end from tst b where b.rowid=tst.rowid-1) Wes On Fri, Jul 10, 2009 at 3:10 PM, 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. > > 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. > > I'm still reading my "Teach Yourself SQL in 10 Minutes" book and just > ordered from LuLu that new book mentioned on this list which will take a few > days to get here. Meanwhile, I was hoping someone could help me with this > question. > > Thank you. > > Rick > > _______________________________________________ > 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
