I have a point shapefile that has many event occurrences stored for ever point. I am trying to select the most recent event for every point. Is there a way to do this through the "Select by Attributes" in ArcGIS?

  • 2
    I don't supposed you are lucky enough to have a datetime stamp field that gets the datetime added when the feature was added, do you? Any sort of date field that can help? – Chad Cooper Feb 28 '12 at 21:11
  • there is a date time field for whe the event occured – Finalfire0 Feb 28 '12 at 21:23
  • How are you storing multiple events for each point? In a separate table or by duplicating points? – blah238 Feb 28 '12 at 21:35
  • duplicating points – Finalfire0 Feb 28 '12 at 21:44
  • My first thought on this is to use Summary Statistics (help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//…) to try and determin the maximum date value for each point. However, you'll first need to convert your dates/times to numeric values, perhaps using the time module in Python. – PolyGeo Feb 28 '12 at 22:57

If you have a identifier that the duplicate points share, you've got it made with a simple SQL query. Just write a sql statement that groups by duplicate point identifier. You could pull your shapefile into a personal geodatabase (*.mdb) with ArcCatalog, then open it with MS Access. Or pull it into PostgreSQL.

 FID | Duplicate_ID | Surname | Created
 1   |  1           | Smith   | 2008-01-01
 2   |  1           | Windsor | 2008-02-01
 3   |  2           | Thorn   | 2008-01-05
 4   |  2           | Baker   | 2008-03-01
 5   |  3           | Sykes   | 2008-01-20

SELECT Duplicate_ID, Forename, Surname
FROM mytable AS t
WHERE Created =
   ( SELECT MAX(Created)
     FROM mytable
     WHERE Duplicate_ID = t.Duplicate_ID )

If you don't have an identifier that indicates duplicates, you're screwed.. Actually you'll just need to do some intersection work with the data first.

"Generate Near Table - Determines the distances from each feature in the input features to one or more nearby features in the near features, within the search radius. The results are recorded in the output table."

"Both the Input Features and Near Features can be the same dataset. In that case when the input and near features are the same record, that result will be skipped so as not to report that each feature is 0 units from itself."



  • There's a HowTo document that may help to explain the technique described by awesomo at support.esri.com/en/knowledgebase/techarticles/detail/31843 - but note that it cannot be used on shapefiles and file geodatabase feature classes unless they are copied into a personal or ArcSDE geodatabase first. – PolyGeo Feb 29 '12 at 1:56
  • I have tried this SQL query with my 'roosts' table and I get the following error: "There was an error with the expression. General function failure [roosts]. Too few parameters. Expected 2." I'm using the select by Attributes function in ArcGIS. Any ideas what I'm doing wrong here please? – ffinnm Mar 26 '14 at 20:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.