File Geodatabase SQL expression to get greatest 1 per group

Question

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 1 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.

Use case: One-to-first joins: Control what related record is used

---

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).

---

Question:

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.

---

Related:

Select greatest n per group using EXISTS

---

Edit:

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.

Answer 1

As you have worked out the SQL used in ArcGIS Pro is different to what is used in database systems, the key issue I think being this statement in the help file

Because you are selecting columns as a whole, you cannot restrict the SELECT to return only some of the columns in the corresponding table because the SELECT * syntax is hard-coded. For this reason, keywords, such as DISTINCT, ORDER BY, and GROUP BY, cannot be used in an SQL expression in ArcGIS except when using subqueries.

So one has to come up with a work around; one such solution I have employed is this:

Run a summary stats tool, this allows you to group data, my demo is shown below:

Run the Add join, join by Asset_ID

Now select where date is equal to max date

Finally if required, remove join

This approach could be very easily turned into a model tool rather than you running the individual tools. The alternative is to use cursors in a Python script.

Answer 2

Here's a FGDB database view that works on a similar table called species_records.

--species_records_latest_vw
select
    *
from
    species_records
inner join
    (select
        t_species,
        max(t_date) as t_date 
    from
        species_records 
    group by
        t_species) l
    on species_records.t_species = l.t_species 
       and species_records.t_date = l.t_date

Unfortunately, I couldn't get it to work as a SQL expression. I guess because it is a correlated subquery, just like my query in the original post above. However, a database view might be useful as an alternative.

Also, the query doesn't break ties when there are multiple rows per species with the same date.

Source: Selecting the most recent records based on unique values in another field