>>>>>Hi All:
>>>>>What's the easiest and most elegant way to remove the SECOND duplicate.
>>>>>IOW, when there is a dupe, get rid of the second record.
>>>>>Thanks,
>>>>>Yossi
>>>>
>>>>
>>>>There may be a better way. I have used this.
>>>>
>>>>

* Prepare the table for the operation
>>>>SELECT tableWithDuplicates
>>>>ALTER TABLE ADD COLUMN lDuplicate L
>>>>
>>>>* Mark everything initially as a duplicate
>>>>REPLACE ALL lDuplicate WITH .T.
>>>>
>>>>* Create an index of unique records
>>>>INDEX ON my+Unique+Key+Fields UNIQUE TAG dupTemp
>>>>
>>>>* Mark every unique record as not being a duplicate (the nature of the INDEX ON command mandates that the UNIQUE records are top-most in row order)
>>>>REPLACE ALL lDuplicate WITH .F.
>>>>
>>>>* Remove the index
>>>>SET INDEX TO
>>>>
>>>>* The records which are marked lDuplicate = .T. are the second (and later) duplicates
>>>>
>>>>* Delete those records and then PACK, mark lDeleted = .T., or whatever...
>>>>
>>>>ALTER TABLE DROP COLUMN lDuplicate
>>>>DELETE TAG dupTemp
>>>>

>>>Sorry Rick,
>>>I am afraid this will not work since Yossi replied the key's are same therefore not unique.
>>>Yossi wants to delete the second record, whatever that means, containing corrupt data.
>>>Better to make a SQL select on corrupt data conditon, however Yossi has not replied what qualifies his data as begin corrupt.
>>>Rgds,
>>>Koen
>>
>>It will work. The key fields I mention are not table keys, but uniquely identifying index keys.
>
>Rick,
>and how does your code know if the record is corrupt? Who says that the second record your code found is the second record?


As I understand it, Yossi said he wanted the second record (and presumably later duplicate records as well) deleted. When you create an index, the first record in the index is the top-most record in the table as per row order. The second record of the same key in the index is the second top-most record in the table, and so on. By marking everything initially as a duplicate, and then performing a UNIQUE index on whatever the field combinations are which make up the unique record condition, then marking those as not a duplicate, all records which remain marked as duplicates are duplicates, and they are the second (and later) duplicates because the records that would've been found by the UNIQUE key condition would've been the top-most records in the table, i.e. the first records.


>Unless we are dealing here with a situation e.g. like:
>table is filled with n correct reccords from a source (table, Excel sheet or what ever)
>appended again with n reccords from a different but wrong source and therefore qualified as 'corrupt'
>therefore the 2nd, wrong, update should be deleted.
>This all is assumptions. Yossi should indicate / confirm.
>Let us wait.
>Rgds,
>Koen