I have a
RoadInsp FC in a FGDB in ArcGIS Pro 2.6.8.
Using an SQL expression in the Select by Attributes tool, I want to select the greatest n per group.
In other words, for a given
ASSET_ID, I want to select the inspection that has the latest
DATE_. The selections in the screenshot reflect what I'm looking for.
I've written a SQL subquery that works as expected on a copy of the table in an Oracle 18c 10.7.1 EGDB:
objectid in ( select a.objectid from roadinsp a inner join (select asset_id, max(date_) as date_ from roadinsp group by asset_id) b on a.asset_id = b.asset_id and a.date_ = b.date_ )
But when I try to use that same expression on the FGDB FC, I get an error:
ERROR 000358: Invalid expression Failed to execute (SelectLayerByAttribute).
Why do I get that error when I use the expression on the FGDB FC?
I've looked at the FGDB SQL syntax in the docs:
- SQL for reporting and analysis on file geodatabases.
- SQL reference for query expressions used in ArcGIS
But as far as I can tell, all of the SQL syntax I've used is supported by the ArcGIS SQL dialect and FGDBs.
I've tested the same expression in ArcGIS Pro 3.0.3 and I get the error there too.
One thing I didn’t consider:
If an asset has multiple rows with the same top date, then only one of those rows should be selected.