>Hi Hilmar,
>
>Back to my old thread..
>
>Could you tell me how to use a MAX() in an SQL to select the Max number of a unique ID in a primary field, (which is programatically used to generate an incremental unique ID for a new record) by including records marked for deletion but not packed?
>
>My problem is that the cursor ignores deleted records when doing a select, and when the ID is generated, the incremental value of the ID could already exist in the non packed table, which then generates a unique conflict error.
>
>If I could have packed my table, I would have no such problems!
>
>Thanks very much,
>
>With best regards,
>
>Steve.

It is better to keep your sequences outside this table. Then, you will have no such conflict. Use a separate table with sequence numbers.

However, another solution - which I do not recommend in this case - is to filter the index on not deleted().

One problem with the approach you suggest is that two users might calculate max(primary_key) at the same time, resulting in the same primary key value.

Another problem is that it is slow. Instead of a select ... max(...), you might also simply use a GO BOTTOM to get the last record. But I still prefer a separate table for the sequences.