SQL LIMIT clause in FGDB SQL

Bud

ArcGIS Pro 3.2.1; file geodatabase

A common scenario in Select By Attributes or in a definition query is to: Select the greatest 1 row per group, no ties.

For example, for each species group, get the row with the latest date. If there are multiple rows per species with the same date, only get one row (arbitrarily).

That can be done in a mobile geodatabase using the LIMIT clause:

species_records.objectid IN (
         SELECT objectid
           FROM species_records r2
          WHERE r2.t_species = species_records.t_species
       ORDER BY T_date DESC  --, priority ASC
          LIMIT 1
                )

And it can be done in Oracle using the FETCH clause:

species_records.objectid IN (
         SELECT objectid
           FROM species_records s2
          WHERE s2.t_species = species_records.t_species
       ORDER BY t_date DESC --, priority ASC
          FETCH FIRST ROW ONLY
                )

But it doesn't seem to be possible in FGDB SQL. Could the LIMIT clause be added to FGDB SQL?

I've attached sample Excel data that can be loaded into a file geodatabase.

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.