Hi sqlite-users :)
I have found sqlite as the best solution to all my data storage problems - a
great piece of software!
Im currently working on a specific algorithm to fill in a table of numbers.
Within the range of an index (idx), I have to count the numbers of rows where
(in1==in2) and place this number in the result column for each subsequent
row, if (in1 != in2) I have to fill in a '0' and restart counting, e.g.
idx | in1 | in2 | result
�1 �| �1 �| �1 �| � 2
�2 �| �1 �| �1 �| � 2
�3 �| �1 �| �2 �| � 0
�4 �| �1 �| �2 �| � 0
�5 �| �2 �| �2 �| � 4
�6 �| �2 �| �2 �| � 4
�7 �| �2 �| �2 �| � 4
�8 �| �2 �| �2 �| � 4
The algorithm works as follows:
��������CREATE TABLE work (idx integer, in1 integer, in2 integer,
result integer);
��������// insert data, order by idx
��������CREATE TRIGGER update_work UPDATE OF result ON work
WHEN new.result == 0
��������BEGIN
����������������UPDATE work
����������������SET result = (SELECT count(*) FROM work WHERE result==-1)
����������������WHERE result == -1;
��������END;
��������UPDATE work
��������SET result = CASE
������������������������WHEN in1 == 0 THEN 0
������������������������WHEN in2 == 0 THEN 0
������������������������WHEN in1 != in2 THEN 0
������������������������ELSE -1
���������������� � � END;
There is one difficulty: I have to rely on the order of updates, say idx==1
has to be updated first, then idx==2 and so on. In principle the algorithm
works - I wouldn't write this mail if there wasn't a BUT:
In a dataset of max(idx) == 220 I found by inserting a line with the current
idx into a triggerlog-table, that updating started at the 125th row, went to
last last (220), got on with the first (1) und stopped at the 124th row -
ough! [The actual work table had some more columns, the index column is 3
columns wide and and there are 4 but 1 result columns - but the principle is
the same ;)].
My question(s): is there a way to urge sqlite to start at idx==1 and increment
as expected? If not is there a better way to achieve the goal of my task?
As an afterthought:
Testing the code above on a newly created table (still 220 rows but less
columns) gives the expected results - maybe an issue with sqlite's memory
management?!
Thanks a lot for any help!
With kind regards
��������Daniel
--
Dipl.-Math. (FH) Daniel Franke
Institut fuer Medizinische Biometrie und Statistik
Medizinische Universit�t zu Luebeck
Ratzeburger Allee 160, Haus 4
23538 Luebeck
Telefon: 0451-500-2786
Telefax: 0451-500-2999
[EMAIL PROTECTED]
