I was trying to figuring out if you are doing something of graph data
analysis, I do it almost everyday in our Stock Trader applications...

I never did this way (direct SQL), cause our graph series data sources are
implement throught a common interface, that could be a SQL query, a stream,
a XML, whatever.

Just a tip: implementing specific and well designed interfaces for series
data manipulation should be the right way for you, avoid scaling issues
cause of possible SQL limitations in the ways those series can/should be
manipulated in some cases.

Just my 2 cents...


-----Original Message-----
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rick Ratchford
Sent: sexta-feira, 10 de julho de 2009 17:32
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Is it Possible in SQL...

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

#>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


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

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

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

I hope this makes sense. I won't be surprised if it does not. :-b



sqlite-users mailing list

sqlite-users mailing list

Reply via email to