Complex SQL subqueries in FGDB SQL expression

Bud

ArcGIS Pro 3.2.1; File Geodatabase:

I have a SQL query that works in a FGDB database view. (Source: Selecting the most recent records based on unique values in another field)

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

 

There are scenarios where I want to use that logic in Select By Attributes; I don't want to create a view.

But as far as I can tell, that SQL doesn't work in a SQL expression subquery.

objectid in (
    select
        objectid
    from
        species_records s
    inner join
        (select
            t_species,
            max(t_date) as t_date 
        from
            species_records 
        group by
            t_species) l
        on s.t_species = l.t_species 
           and s.t_date = l.t_date
    )   

ERROR 000358: Invalid expression

 

That SQL expression works in a mobile geodatabase and enterprise geodatabase, but not in a file geodatabase.

Mobile geodatabase:

 

Could FGDBs be enhanced so that they support that kind of complex subquery?

species_records.xlsx

MMBR

My 2 cents. Certain SQL operations are only available in a RDBMS (Oracle, SQL Server, PortgreSQL) because the SQL Engine behind the RDBMS that is quite complex. Certain SQL functions in a File Geodatabase might not be available and it will be difficult to implement that because of this reason. Therefore, the best aproach is move the data from the File Geodatabase to an Enterprise Geodatabase, even Mobile Geodatabase will present some limitations as well, thus move the data to an Enterprise Database (Oracle, SQL Server, PostgreSQL). I hope this helps.