Re: Problem with SQL

I have noticed this behavior before (back in the 3.3 days, actually). It may depend on the order in which you delete the rows. If the way that C-ISAM handles deleted rows in the .dat file is to keep a linked list of empty rows, then you could see this kind of physical row ordering under certain circumstances. With the linked list approach, when the user "deletes" a row, it is placed at the end of the list. When the user "adds" a row, the empty row list is checked first so that the file won't be physically extended unless it is really full. Depending on how you implement the list, it's usually easiest (and quickest) to use the row most recently added to the list in a last-in-first-out fashion. Thus empty rows will be re-used in the reverse order in which they were "deleted." For example, suppose you add row-1, row-2 and row-3 in that order to a newly created table. Then you delete them in the same order. At that point, the linked list of empty rows is in the order 1, 2, 3, with row-1 at the head and row-3 at the tail. When you add row-4, row-5 and row-6, the empty rows are popped off the list in the order 3, then 2, then 1. At that point, the physical order in the .dat file is row-6, row-5, row-4 since row-4 went into row-3's old slot, row-5 went into row-2's old slot, and row-6 went into row-1's old slot. I've had situations where the users needed to see rows in the *chronological* order in which they were entered. One that comes to mind is an accounting application where transactions were batched, then posted. Each batch of transactions would be entered from a stack of source documents and/or a data entry sheet. The pre-posting edit listings had to be in the same order as that in which the data was entered, which could be anything. In that case, I was tagging each row with a SERIAL field for a transaction ID, so I just used that (along with a batch number). You might try something like that. I only use ISQL occasionally, so I don't know how sperform decides what order so use. The 3.3 product had the concept of a "primary" index. If you define the SERIAL field first in the table's schema and create a unique index on that field as the first index, maybe sperform will grab it. Failing that, at least you'll be able to order reports the way you want. Hope this helps, Walt. -- Walt Hultgren Internet: walt@rmy.emory.edu (IP 128.140.8.1) Emory University UUCP: {...,gatech,rutgers,uunet}!emory!rmy!walt 954 Gatewood Road, NE BITNET: walt@EMORY Atlanta, GA 30329 USA Voice: +1 404 727 0648