Support correlated subqueries in file geodatabase SQL expressions

[Bud]

Could FGDB SQL be enhanced to support correlated subqueries?

Example: Using an SQL expression in the Select by Attributes tool, I want to select the greatest 1 per group.

NOT EXISTS (
        SELECT *
        FROM road_insp r2
        WHERE r2.asset_id = road_insp.asset_id AND r2.date_ > road_insp.date_
       )
--Demo:  https://dbfiddle.uk/gSGjLTCW 

The above SQL expression would work as expected in mobile GDBs and enterprise GDBs. But in file GDBs, it would select all rows in the table, which would be incorrect. 
It’s important to note that the query fails silently, producing incorrect results, rather than throwing an useful error.

Related: Clarify the docs: Correlated subqueries not supported in FGDB SQL expressions

Could FGDB SQL be enhanced to support correlated subqueries?

Thanks.

[JoshuaBixby]

After years, more than a decade, of Esri not really responding to requests to improve SQL support and documentation of SQL support for file geodatabases, I would not be holding one's breath.  Now that mobile geodatabases are getting more support across ArcGIS product lines, I am using them more and more.

[Bud]

@JoshuaBixby @Do you find there’s functionality that’s lacking in mobile geodatabases that fgdbs supported?

[JoshuaBixby]

Regarding your question, yes, but the answer is slowly changing to, no.  Not all ArcGIS products  support mobile geodatabases and some only support a limited subset of functionality with mobile geodatabases.  ArcGIS Desktop/ArcMap is a good example with its very limited support for mobile geodatabases.  Over time older products like ArcGIS Desktop/ArcMap will be retired and most new ArcGIS products or versions are supporting more functionality with mobile geodatabases.  In the end, I suspect file geodatabases will always perform better than mobile geodatabases, but mobile geodatabases offer much better SQL support, so one will have to weigh the tradeoffs when deciding.

[Bud]

BUG-000156143 - An SQL query containing the EXISTS predicate validates successfully but returns incorrect results from a file geodatabase.

ENH-000156158 - Raise exception when using unsupported SQL expressions in file geodatabases.

Esri Canada Case 03264937 - Exception not raised when unsupported FGDB SQL used (correlated subquery)

[Bud]

Here’s an example use case where a FGDB correlated subquery might have saved the day:

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