delete multiples, keep duplicates

[Mark Morrison]

I know it sounds strange. I have a table that lists all of the features that
have been edited. Everytime a feature has been edited, a new record is
created. I would like to trim this table so that only 2 copies of a given
record are represented (because the features sometimes come in pairs) and
delete all occurrences of 3 or more. Here is a sample of the table:
OBJECTID FEATURECLASS IPID DATE_DELETED
1554 STREETSURFACE 793154214 10/6/2009 4:24:57 PM
666 STREET 793154214 9/30/2009 11:07:54 PM
469 STREETSURFACE 793154214 9/30/2009 1:37:28 AM
468 STREETSURFACE 793154214 9/30/2009 1:37:28 AM
1555 STREETSURFACE 793154214 10/6/2009 4:24:57 PM
The IPIDs repeat for 793154214 for the feature STREETSURFACE. I only want to
keep 2 of the the STREETSURFACE records (any 2) and delete the others. I also
want to keep any STREET records but I know how to get around this issue.

Any help would be appreciated. Thank you.

 

[John Spencer]

Perhaps something like the following could be used to identify which records
to keep.

SELECT MIN(ObjectID) as KeepObjectID
FROM SomeTable
WHERE FeatureClass = "StreetSurface"
GROUP BY IPID
UNION
SELECT Last(ObjectID)
FROM SomeTable
WHERE FeatureClass = "StreetSurface"
GROUP BY IPID

Once you have done that, you can join the results of the union query back to
your main table.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County