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?
