Delete duplicates and keep latest record (MAX ObjectID)

514
6
Jump to solution
08-27-2021 06:43 AM
REDARiahi
New Contributor

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?

0 Kudos
1 Solution

Accepted Solutions
JayantaPoddar
MVP Esteemed Contributor

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.



Think Location

View solution in original post

6 Replies
JayantaPoddar
MVP Esteemed Contributor

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.



Think Location
REDARiahi
New Contributor

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

0 Kudos
JayantaPoddar
MVP Esteemed Contributor

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.



Think Location

View solution in original post

REDARiahi
New Contributor

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?

0 Kudos
FelixPertziger
New Contributor III

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

REDARiahi
New Contributor

It finally worked! Thank you Felix and Jayanta

0 Kudos