I haven't found a way to do it in a SQL expression. But I got a FGDB database view to work.
I thought I'd tried this approach previously without luck. But I guess I must have done something differently this time because it works now.
1. Load this sample spreadsheet into a FGDB:
https://community.esri.com/t5/arcgis-pro-ideas/fgdb-database-views-view-that-selects-from-an/idi-p/1...
2. Create this database view:
--species_records_latest_vw
select
*
from
species_records
inner join
(select
t_species,
max(t_date) as t_date
from
species_records
group by
t_species) l
on species_records.t_species = l.t_species
and species_records.t_date = l.t_date
For my notes, the SQL in brackets is a derived table, not a subquery. Is the SQL in brackets a subquery?
Result:
Unfortunately, the query doesn't break ties when there are multiple rows per species with the same date.
Video:
To use the result in further analysis, it might be best to export the view as a table since FGDB views are buggy in ArcGIS Pro. For example, joining from a table to a view doesn't seem to work; the joined fields are all null. Idea: Join to a FGDB database view.
Update:
I got a FGDB database view working that breaks the ties.
Again, I thought I had tried this previously and failed. But it seems to be working now.
select
*
from
species_records s
inner join
(
select
min(objectid) as objectid,
min(t_unique_id) as t_unique_id,
t_species,
max(t_date) as t_date
from
(select
*
from
species_records
inner join
(select
t_species,
max(t_date) as t_date
from
species_records
group by
t_species) l
on species_records.t_species = l.t_species
and species_records.t_date = l.t_date
)
group by
t_species
) l
on s.t_unique_id = l.t_unique_id
Some of the fields have asterisks (*) beside the field names, indicating the field has an index.
I don't think I added those indexes intentionally. But maybe it happened when using a GP tool. I don't know if that helps these views work better or not. But it's something to keep in mind.
An observation:
Sometimes, SQL table aliases behave strangely in FGDB views. For example, SPECIES_RECORDS S and S.* . Field headers in the attribute table will occasionally show a S. prefix. Other times it won't show a prefix. Sometimes joined SQL fields with duplicate field names will show up in the attribute table, other times not. I don't know what the reasoning is behind that, but it tells me aliases and field names are buggy. If I'm having trouble creating a FGDB database view, then I'll fiddle with the aliases in the SQL. I'll change SELECT S.* to just SELECT * . Or something like that.
Maybe it helps, maybe it doesn't.
Related: