Select maximum values in Select By Attributes (greatest n per group)
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.
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 NULLERROR 160195: An invalid SQL statement was used.Of course, I don't have this problem in mobile or enterprise GDB database views.
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
)
