Idea: Need 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.
I don’t want to create a new FC or create a database view. I want to make a selection in the attribute table of an existing FGDB table.
Related question: GP tool to select greatest 1 per group
Edit:
The tool should also let you break ties. So, if for a given asset there were multiple rows with the same date, then use a different field to determine what row to use (aka what row to break the tie).
Similar to what the SQL expression in this answer does: Using a WHERE clause subquery, select the greatest 1 per group with a tie-breaker
roadinsptable.objectid IN (
SELECT objectid
FROM roadinsptable r2
WHERE r2.asset_id = roadinsptable.asset_id
ORDER BY date_ DESC, condition DESC
LIMIT 1
)That SQL expression (or similar) works for mobile GDBs and enterprise GDBs, but not for file GDBs. So we need a geoprocessing tool.
There are cases when the tiebreaker would be arbitrary. Any of the ties could be used since we don't always have a different field that can be used to break the tie.
But there are other cases where we do have an additional field that we want to use to break the tie (get the max or min of the tiebreaker field per group). So we need the option to use additional fields as tiebreakers.
The tool needs to work for standalone tables.
