Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 6 of 6 total |
Update all but latest |
Mon, May 20 2013 9:27 AM | Permanent Link |
Matthew Jones | 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/ |
Mon, May 20 2013 11:04 AM | Permanent Link |
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/ |
Mon, May 20 2013 11:34 AM | Permanent Link |
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/ |
Mon, May 20 2013 1:28 PM | Permanent Link |
Fernando Dias Team Elevate | 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] |
Mon, May 20 2013 3:00 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Tue, May 21 2013 4:26 AM | Permanent Link |
Matthew Jones | Thanks both.
/Matthew Jones/ |
This web page was last updated on Monday, April 22, 2024 at 04:13 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |