>I have a payment servicing application that has a fairly simple table structure: payor.dbf for the parent table and payment.dbf for the child table. Both are free tables with structural CDX index files.
>
>About one time out of 200, a record is appended to the payment table (no relations set at the time) and the CDX appears not to have been updated. At the time of recording, the payment.dbf is opened briefly relying on Fox's ability to lock the table, the record is appended and the table is closed. No one ever sees contention errors.
>
>If I do a report that uses SQL-SELECT and GROUP the result, the next to last item instead of the last item appears in the report for one payor out of the 200. When an aging report is run, the payor looks to be late. When a history is run, the payment is there properly recorded.
>
>It looks like either SQL is broken or updating CDXs is broken from within FoxPro, but I can't believe that either scenario is likely or hundreds of others would have seen this behavior before now. I am out of other ideas.
>
>Any input is greatly welcome.

SQL Group will select the last physical record for each group, i.e. it doesn't matter how good is your index, you must provide the needed sequence in the table itself. Also note that SELECT-SQL doesn't respect current SET ORDER. The way to do is to run 2 Selects. First one will just ORDER records into cursor, and second SELECT will group this cursor.