topic Re: Delete duplicates and keep latest record (MAX ObjectID) in Data Management Questions

Delete duplicates and keep latest record (MAX ObjectID)

REDARiahi — Fri, 27 Aug 2021 13:43:32 GMT

I have a dataset with daily COVID-19 data for the countries of the Americas since the beginning of the pandemic. I have many duplicated records (same ISO3_CODE and Report_Date) and I want to keep the most recent record for each. Ideally, I would like to this with ModelBuilder, if possible.

I tried DELETE IDENTICAL tool, but unfortunately it keeps the first duplicate and deletes the others. I want the exact opposite: Keep the last duplicate (MAX Object ID) and delete the previous ones.

Any suggestions?

Re: Delete duplicates and keep latest record (MAX ObjectID)

JayantaPoddar — Fri, 27 Aug 2021 14:03:24 GMT

First, you could Sort by ObjectID field in descending. Then try doing a Delete Identical.

You could add them in a Model Builder, and create a model.

Re: Delete duplicates and keep latest record (MAX ObjectID)

REDARiahi — Fri, 27 Aug 2021 16:45:00 GMT

Unfortunately, that didn't work. It still keeps the lowest (MIN) ObjectID.

Re: Delete duplicates and keep latest record (MAX ObjectID)

JayantaPoddar — Fri, 27 Aug 2021 19:25:22 GMT

I hope you have used the Sort Geoprocessing tool.

After sorting the features in descending order of ObjectID, the latest feature should be having the lowest ObjectID in the newly created feature class.

That was my idea.

Re: Delete duplicates and keep latest record (MAX ObjectID)

REDARiahi — Wed, 01 Sep 2021 16:46:29 GMT

Thanks for tip.

However, I'm still having issue deleting the duplicates.
SORT can't use ObjectID as sorting field. I had to ADD FIELD (ObjID), then assign it ObjectID value. After that, I was able to SORT using ObjID. However, when using DELETE IDENTICAL on the sorted feature layer, it deletes the rows from the sorted table, not the feature layer itself...

Is there an efficient way to apply the sorting to the original feature layer?

Re: Delete duplicates and keep latest record (MAX ObjectID)

FelixPertziger — Sun, 12 Sep 2021 02:29:05 GMT

Join it back to original. Delete records that don't match.

Re: Delete duplicates and keep latest record (MAX ObjectID)

REDARiahi — Tue, 21 Sep 2021 11:58:18 GMT

It finally worked! Thank you Felix and Jayanta