I want to track the movement of icebergs in ArcGIS. In my FileGeodatabase I have a single Iceberg layer. What I have thought of is to create an ID field for each object; each ArcGIS object that's the same iceberg will have the same ID. They also have a Date field to indicate when they've been observed.
I would like to only show the latest observation of each object (in ArcGIS terms, for each ID, show the OBJECTID with the latest date).
Using definition query, I've set the where clause to:
SELECT * FROM Icebergs WHERE: Date = (SELECT MAX(Date) FROM Icebergs m WHERE m.ID = ID)
However no results are returned. Clicking verify shows that the query is syntactically correct. Is this subquery too complex for ArcGIS? Is Definition Query is the correct approach? If not, please point me in the right direction. Later I plan to do this using the Esri JS API, but am using ArcMap to debug the query itself.