I get data with a "user" and a "value", and I put it in a table.
I need to know which is the latest, so I can ignore the previous in calculations.
This would be easy were it not for a quirk. The easy part would be to simply update
any existing one to mark it as "old" before storing the new one. But the quirk is
that I have to limit the changes to a variable number. So any user can change their
mind say 5 times, where the first 4 are ignored, the 5th is taken as the "final
answer", and the 6th and later are ignored too.

Given that, is there any sensible way to do this with a DBISAM query? Or should I
code it?

In essence, I want to be able to say "UPDATE the TOP 4 to set them old", then store
the data, then perhaps "UPDATE all but the TOP 5 to set them ignored". Is there
even an "anti-TOP" facility, perhaps "BOTTOM"?

The way that my summary SQL works it to use only the "current" values, and that is
all quite complicated, so it can't be done there. Plus updating only when a new
input is received is optimal in that regard.

All suggestions welcome.

/Matthew Jones/

My first attempt seems to be failing.

I can do a COUNT to find out how many items there are, so that is easy.

If more than one, then I would like to update all before the last, but I fall over
here. The basic query that gets me what I want is this:

SELECT qrResponseValue, qrResponseTime, qrQuestionID
FROM QuestionResponses
WHERE qrQuestionID = 3205506
ORDER by qrResponseTime
TOP 5

Which thus leads to the following update statement:
UPDATE QuestionResponses
SET qrResponseActive = 2
WHERE qrID IN (SELECT qrID, qrResponseValue, qrResponseTime, qrQuestionID
FROM QuestionResponses
WHERE qrQuestionID = 3205506
ORDER by qrResponseTime
TOP 5)

Unfortunately, this fails as I can only have one item in the Sub-SELECT. Which
doesn't work, as I have to have the others to allow the order by to work. I just
tried moving the sub-where to the outer select, and it solved that, but not the
qrResponseTime sort, needed for the TOP.

Doing the later ones is easy using DESC on the qrResponseTime sort and a TOP, but
not if I can't do an easy update. I guess it might be that I have to process them
manually.

Ooh! Writing this made me wonder about the qrID - since that is in the same order
as the time, I can order by that instead.

This works:!

UPDATE QuestionResponses
SET qrResponseActive = 2
WHERE qrID IN (SELECT qrID
FROM QuestionResponses
WHERE qrQuestionID = 3205506
ORDER by qrID
TOP 5)

Just have to sort my indexes to make it efficient. Thanks all for helping! (I'll
post this anyway so I can find how I did it in years to come.)

/Matthew Jones/

"The expression:

qrID IN SELECT qrID FROM QuestionResponses WHERE (qrQuestionID = 3205963) AND
(qrResponderID = ?) ORDER by qrID TOP 5

is UN-OPTIMIZED and will be applied to each candidate row in the result set as the
result set is generated"

Is there anything I can do to make this optimised? Or is it just the way it works?
It only has to do it for 13 rows it says (the number of data rows found), but even
so I like to keep my database stuff as optimal as possible.

/Matthew Jones/

Mathew,

Yes, add indexes to the QuestionResponses table on qrQuestionID or qrResponderID or both - just test it and see if it woths adding both or not, depending on the query execution time and how often you will use oner or the other indexes.

--
Fernando Dias
[Team Elevate]

Matthew,

<< Is there anything I can do to make this optimised? Or is it just the way
it works? >>

You can optimize the actual sub-select, but the IN expression is just like
the execution plan says: it has to be executed for every row in the outer
query after all of the more optimized expressions are executed (which is
none, in this case).

Tim Young
Elevate Software
www.elevatesoft.com

Thanks both.

/Matthew Jones/