#>-----Original Message----- #>From: sqlite-users-boun...@sqlite.org #>[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik #>Sent: Friday, July 10, 2009 2:50 PM #>To: sqlite-users@sqlite.org #>Subject: Re: [sqlite] Is it Possible in SQL... #> #>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 #>
Whoa. Okay, this is going to take me a bit to figure out. I was expecting to read, "no you dummy. it can't do stuff like that." I should have known better. Do you think this kind of stuff will be in that new book we've been hearing about last couple days? Thanks again Igor. Something to really sink my teeth into! :-) Rick _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
