Select records with most recent date for groups of points

13591
12
05-12-2017 02:49 PM
JasonFultz
New Contributor III

Hi all, 

I am working with a feature class (sample_Locations) in an ArcSDE Geodatabase and my table has a "Point" field and a "Date" field, the field types are string and date, respectively.  There are over 2000 features in the table and the point ID's are not unique, there are only ~100 point ID's, so there may be many points with the same Point ID.  I am trying to select records from the table with the most recent date for sets of values, where the field "Point" is the field that contains the values that define groups.

I have tried using the following subquery, but it did not select the correct records.  It selected a very random assortment of features.

[Date] in (SELECT max( [Date] ) FROM Sample_Locations GROUP BY [Point])

Can anyone provide help for performing this selection using a Python script?  Ideally, I would like to select the features and export these selected features to a standalone excel table.

Any help would be greatly appreciated!!

Jason

12 Replies
BlakeTerhune
MVP Regular Contributor

You could use the dissolve geoprocessing tool with Point as the dissolve field and and specify Date as the statistics field with the MAX statistic type.

EDIT:

Since you just want a flat file, you can save the dissolve output to memory, make a table viewexport it to Excel, then clean up the mess.

JoshuaBixby
MVP Esteemed Contributor

Since you are using an enterprise geodatabase, it is fairly straightforward using SQL NOT EXISTS.  See https://community.esri.com/thread/169714 for example code.

JoshuaBixby
MVP Esteemed Contributor

Replace fc in the following with the actual name of the table:

qry = "NOT EXISTS (SELECT 1 FROM fc f WHERE fc.point = f.point AND f.date > fc.date)"  
arcpy.SelectLayerByAttribute_management(layerName, "NEW_SELECTION", qry) 

Given that "Date" can be both a data type and function depending on the languages involved, I strongly discourage the use of it as a field name.  If you haven't run into issues already, I expect you eventually will run into some.  Also, "Point" is typically thought of in a spatial sense in GIS systems.  Having a text field named Point and storing non-spatial values in it, might create some confusion or usability issues down the road.

NicholasReseburg1
New Contributor III

I just wanted to follow up on this because I needed to do this and the above sql failed for me.  So in case anyone out there is looking for a SQL solution contained in a def query, I re-wrote the query to something that worked (for me), remembering this will only work forfeature classes stored in a  SDE, not a file geodatabase 

replace:

featureclass  the name of your feature class

Point The name field for your feature class, in the original question called "Point"

DATE The date field of your feature class you are trying to get the latest date of

OBJECTID IN (SELECT OBJECTID FROM featureclass a JOIN (SELECT Point, MAX (date) DATE FROM featureclass GROUP BY Point) b ON a.Point = b.Point AND a.DATE = b.DATE)

Jol1234
New Contributor II

I had issues making this work as displayed above and would suggest the following changes just to make a bit more clear regarding what is a field in feature class vs what is a text from the query. Great solution though and thank you. 


OBJECTID IN (SELECT OBJECTID FROM featureclass a JOIN (SELECT point, MAX(datefield) as 'DateField' FROM featureclass GROUP BY point)b on a.point = b.point AND a.datefield = b.DateField)

 

and since MAX ignores Null values I also tagged on a "AND datefield IS NOT NULL" to the end

DaleSmith
New Contributor

Thanks! This helped immensely. Below is the code that worked for me.

feature class: WaterQuality

point field: StationID

Date field: SampleDate

OBJECTID IN (SELECT OBJECTID FROM WaterQuality a JOIN (SELECT StationID, MAX(SampleDate) DATE FROM WaterQuality GROUP BY StationID) b ON a.StationID = b.StationID AND a.SampleDate = b.DATE

0 Kudos
AndresCastillo
MVP Regular Contributor

Mr. Bixby,

Maybe you can contribute to a thread I am involved in with Jerry Corum on May 17, 2018 12:11 PM:

https://community.esri.com/message/772784-re-collector-use-related-layer-to-symbolise-feature?commen...

I think we are trying to use a SQL query with no python.

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

Andres, I took a look at the thread you referenced, and I think it might be best to start a new, clean thread.  The thread is from 2015 and has comments spanning all years since.  Unless I am misreading comments, the thread seem to wonder in terms of topic, which is why I wonder if starting a new thread and asking your question might be easier for people to response to.

0 Kudos
AndresCastillo
MVP Regular Contributor
0 Kudos