What SQL standard to use when writing geodatabase-agnostic SQL expressions?

[Bud]

What SQL standard should we use when writing geodatabase-agnostic SQL expressions? (I.e. SQL:2016, etc.)

For example, I want to write an SQL expression that will work in mobile GDBs and all major enterprise GDBs (Oracle, SQL Server, and PostgreSQL) that will select the greatest n per group when used in Select by Attributes.

I have what I assume is a geodatabase-agnostic expression that works, to a degree, for getting the greatest n per group:

--SQL expression:
date_ = (select max(subq.date_) from roadinsp subq where roadinsp.asset_id = subq.asset_id)
--https://dbfiddle.uk/HpqMb2ls

That works, but it selects multiple/duplicate rows for a given asset in this scenario: the asset has multiple top rows with the same date.

I would rather the query only select one row for each asset.

For that requirement, I think the SQL will get more complicated, at least if I want to control what duplicate will break the tie — i.e. what specific row will be used. I can think of ways of doing it fairly simply in, say, Oracle. But the SQL would only work in Oracle GDBs, not others.

So that makes me wonder, as a starting point, what SQL standard should I use to make geodatabase-agnostic SQL expressions?

[AyanPalit]

@Bud Recommend using SQL reference

[JoshuaBixby]

The SQL that is supported in ArcGIS applications has always depended on the back-end database/datastore.  The SQL reference for query expressions used in ArcGIS - ArcGIS Pro | Documentation clearly states:

Review the following to help determine when ArcGIS SQL syntax is used or when the SQL syntax of the underlying RDBMS is used when creating an SQL expression.

• If the data within your SQL expression comes from a mixture of data source locations, the following will occur:
  • Where the data sources come from both file-based and from an RDBMS, ArcGIS SQL syntax will be used.
• If all the data within your SQL expression comes from the same data source location, the following will occur:
  • Where the data source is file based data, ArcGIS SQL syntax will be used.
  • Where the data source is a database or enterprise geodatabase, ArcGIS will pass the SQL expression to the RDBMS for resolution and you will need to consult the documentation for your database management system for the specific expression syntax and data types supported.

There is no minimum SQL standard at the geodatabase level.  If you are interested in SQL that is portable across multiple databases/datastores hosting geodatabases, then you need to look at the versions of those databases/datastores and determine what versions of SQL they support.

Even viewing this as a database question and not a geodatabase question, the answer isn't straightforward because I don't think a single DBMS fully supports any specific version of the SQL standard.  Most database software companies like to use language similar to "provides full or partial conformance to SQL-XX," and one has to look deeper to see what exactly is supported.