Hi all,

I have a feature class and a table (sites and monitoring records) in an enterprise GDB.

I'm trying to create a definition query to filter the feature class by the status of the most recent monitoring visit in the related table.

I'm kind of at a loss for how to do it. (Side note: this would be easier if the SQL window were improved Improve SQL Expression Builder - Esri Community)

I'm thinking that the best way would be to:

1. Group the monitoring data by site number,
2. Take the MAX date from the grouped monitoring data,
3. Join the filtered monitoring records to the feature class
4. Filtering based off the joined field where the Status is equal to [Status].

Unfortunately, I am pretty bad at SQL and have no idea where or how to do this.

I kind of have an expression for the subquery to filter by date:

• (SELECT MAX(Mon_Date) FROM Monitoring_Table GROUP BY Site_ID)

But nothing else, and I'm not even sure if this works. SQL reference for query expressions used in ArcGIS—ArcGIS Pro | Documentation mentions that joins are tricky in enterprise GDBs, which adds to another layer of confusion.

If anyone has any insight into this, I'd appreciate it.

Thanks!