delete multiples, keep duplicates

  • Thread starter Thread starter Mark Morrison
  • Start date Start date
M

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.
 
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
 
Back
Top