> On 31. jan. 2015, at 13.50, Bob M <[email protected]> wrote: > > Hi Dyre > > Thank you for your explanation > I understand completely > My concept of requiring the table to look exactly as I think it should look > before any ORDER BY is WRONG!!!!!!!!!!!!!!!
Well, its an intuitive way to think about it. That is why some dbs have ROWID. It is easy to think about a table as a vector, but it is really more like a hashtable, where there is no implied order and you need to a key to access the record you’re interested in. > I do, in fact, retrieve x records using ORDER BY exactly as you say to > always get the x latest records. > > Now one last question, if I may................. > After adding a new record, I should like to set the pointer to the > penultimate latest record ordered by DATE and Time so that I can update the > Profit field > Exactly what code do I need to do that, please > I will not say that it is impossible, but probably hard. As you probably know, there isn’t really a concept of pointer - the closest thing is a SCROLLABLE UPDATABLE result set. Unfortunately, I don’t think Derby supports ORDER BY with scrollable result sets (I may be wrong, check the docs), so that probably won’t help much… But perhaps there is a simpler way to do what you’re attempting? So you insert a record for a trade, and then after a while (when the trade has gone through?) you calculate the loss/profit and update the record? Assuming this is the situation, sort of, here is what I would consider: 1) Leave a “not calculated yet” value (NULL comes to mind) in the profit column. Then issue an UPDATE … WHERE PROFIT IS NULL. No need to know where the record is, as long as you update the right one… will obviously only work as long as there is only one record with NULL profit at a time... 2) Maybe there is an ID/TRADE NUMBER here? Presumably you could track (in your app) the ID of the trade(s) which has/have not been finalized? Then you could do someting like UPDATE … WHERE ID = <last trade id> 3) A separate table for the unfinished trades. Update when you know the profit, and copy to the real table… HTH, Dyre
