Select maximum values in Select By Attributes (greatest n per group)

[Bud]

ArcGIS Pro 3.2.1; mobile geodatabase

I'm trying to learn about different ways to select the greatest n per group in ArcGIS Pro. I came across this old ArcMap article that talks about a possible technique:

Select minimum and maximum values in the Select By Attributes window in ArcMap

Use GROUP BY subquery clauses to return values from aggregate functions on sets of values.

Example #3:

Select the records from the table with the most recent date for sets of values, where <group_ID> is the field that contains the values that define the groups:

<date> = (SELECT MAX(date) FROM <table_name> GROUP BY <group_ID>)

I'll use the SQL expression above to try to select the latest row per species group. The result looks like this:

t_date = (
    select 
        max(t_date) 
    from 
        species_records
    group by
        t_species)

That's not the result I was looking for.

This would be the result I'm looking for:

t_date in (
    select
        max(t_date)
    from
        species_records s2 
    where
        s2.t_species = species_records.t_species)

What am I missing in that article?

Notes:

• I'm aware that there will be duplicate rows selected per group if there are multiple rows per species that have the same date. Breaking the tie (arbitrarily) would be ideal. But I don't know if that's possible with this particular approach.
• I've attached sample Excel data that can be loaded into a geodatabase.

Thanks.

[Bud]

Esri Case 03529800 - FGDB database view: Can't use CAST function in WHERE clause

 
BUG-000164429: When creating a database view, using a cast function in the where clause results in an error

[Bud]

Regarding not being able to create a database view with CAST in the WHERE clause, that is interesting because CAST works in the Select By Attributes tool, which of course is the WHERE clause:

CAST(t_date AS CHAR(20)) IS NOT NULL

I looked at the Diagnostic Monitor in ArcGIS Pro to see what SQL query Pro sends to the database when using CAST in Select By Attributes. I had wondered if it was doing something special, such as wrapping something in a subquery or outer query, which can be an effective workaround in some cases.

Examples:

• Create FGDB database view using the ORDER BY clause: ERROR 999999
• There is an Esri bug where ST_GEOMETRY functions in Oracle can’t be used in a SQL Expression (definition query, attribute table, etc.) when the FC is versioned. Wrapping the WHERE clause in a subquery lets us work around that bug.
  • Case #03080875 - ST_GEOMETRY functions used in Select By Attributes tool on Shape field generate error "ORA-00904 Invalid Identifier "SHAPE"
  • BUG-000150273
  • https://community.esri.com/t5/arcgis-pro-questions/find-multi-part-features-using-sql-st-geometry/m-...

But no, the query looks normal.

SELECT OBJECTID FROM species_records WHERE CAST(t_date AS CHAR(20)) IS NOT NULL

I tried using that exact SQL to create a FGDB database view. But it failed, just like previous attempts to use CAST in the WHERE clause of a FGDB database view.

I also tried adding an extra column to the SELECT clause, just in case it didn't like only selecting the ObjectID. But that didn't work either.

SELECT OBJECTID, t_species FROM species_records WHERE CAST(t_date AS CHAR(20)) IS NOT NULL

ERROR 160195: An invalid SQL statement was used.

Of course, I don't have this problem in mobile or enterprise GDB database views.

[Bud]

Surprisingly, wrapping the WHERE clause CAST in a subquery seems to work. I'm able to create the view.

select
    *
from 
    species_records 
where
    objectid in (
        select
            objectid
        from
            species_records
        where
            cast(t_date as char(20)) is not null
        )

[Bud]

Possibly related: Selecting maximum values of a field, by group