This will create another table TmpTable (tax, direction), using the values from the table MarketTable:
create table TmpTable as select tax, (select case when b.tax < MarketTable .tax then "Up" when b.tax>=MarketTable .tax then "Down" else null end from MarketTable b where b.rowid=MarketTable.rowid-1) from MarketTable If you need to include other columns, you can list them at the top level select. Wes On Fri, Jul 10, 2009 at 4:31 PM, Rick Ratchford<r...@amazingaccuracy.com> wrote: > Seems there was a question in your reply I didn't catch the first time. > >> > #>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? > > The table, each time, has been in order from oldest Date to latest Date. > There is also the Primary ID field that starts from 1 to ... > > The table is created by loading in a Metastock data file. The table name is > that of the market loaded. Records are never deleted or inserted within this > table, as is simply houses the complete price data loaded for that specific > market. So the order stays as when first created. It is never modified. From > oldest date to last price data date. For simplicity, call this MarketTable. > > What I've been working on are the recordsets created from MarketTable, or a > temp table (TmpTable) that was created from a recordset derived from the > MarketTable. > > #>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 > > > After examining the above, it appears that what this does is modify the > table itself. So I suppose then that it is not possible to create a > recordset instead that meets what I'm trying to do. If this is the case, > I'll have to make a copy of this table first as I don't want to modify the > original. > > ============================ > > Here's what is going on: > > Most of my functions and procedures works off a table called TmpTable. This > was created from a recordset derived from the main MarketTable. TmpTable was > created because it needed to be somewhat modified from the original price > data table (MarketTable). > > The current procedure that prompted my original question needs only to > determine the DIRECTION of values from one record to the next. Once this > procedure has completed this task and plotted (graphically), it is no longer > needed. > > So modifying TmpTable, which will still be needed for other procedures, is > not preferred. It would be great if a recordset could be derived from it > instead that contains the DIRECTION results. Once the procedure exits, the > recordset would just go away. > > My original plan was to create the recordset from TmpTable, with the added > DIRECTION column. > > Ex: 0 as Direction FROM TmpTable > > Then, either loop through the recordset doing the comparisons and filling in > DIRECTION, or make an array copy of the recordset and then fill in the info. > > Of course, it is preferred that it be already accomplished by way of the > query. > > I hope this makes sense. I won't be surprised if it does not. :-b > > Thanks. > > 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
