topic Re: GP tool to select greatest n per group in ArcGIS Pro Questions

GP tool to select greatest 1 per group

Bud — Mon, 15 Jan 2024 07:14:10 GMT

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

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

File Geodatabase SQL expression to get greatest 1 per group [ERROR 000358: Invalid expression]
Idea: Geoprocessing tool to select greatest 1 per group (without creating a new FC)
Selecting the most recent records based on unique values in another field

Re: GP tool to select greatest n per group

Robert_LeClair — Tue, 06 Dec 2022 16:53:26 GMT

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.

Re: GP tool to select greatest n per group

Bud — Sun, 21 Jan 2024 08:47:26 GMT

Thanks!

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

FYI - The SQL syntax in that article appears to be incorrect: Select maximum values in Select By Attributes (greatest n per group)

Also, for my notes, the Dissolve tool doesn't seem to work for standalone tables. But the Summary Statistics tool does.