GP tool to select greatest n per group

12-06-2022 08:09 AM
Labels (1)
Regular Contributor III

Is there an OOTB geoprocessing tool that can be used to select the greatest n per group in a FGDB table?

For example, for a given ASSET_ID, select the road inspection that has the latest date.


Related: File Geodatabase SQL expression to get greatest n per group [ERROR 000358: Invalid expression]

Idea: Geoprocessing tool to select greatest n per group (without creating a new FC)


0 Kudos
1 Reply
Esri Notable Contributor

I know you mentioned you're using a fGDB but if you were using an enterprise gdb (egdb), then you could use SQL to do this.

There's an article here that explains the syntax to use - <date> = (SELECT MAX(date) FROM <table_name> GROUP BY <group_ID>)

The article does say if you create an OLE DB connection to a MS Access table then you could use the SQL syntax above.

Looking for a fGDB solution right now.

UPDATE:  It seems with Python you can do this - here's a link here that shows a labeling example.  I'm not a Python person but I suspect many on this forum are.

UPDATE 2:  For fGDB data, use the Dissolve GP tool with the Maximum value set for the Statistics Field Parameter.  That should work for you BUT it does create a new FC unfortunately.