Layer Definition Query - Subqueries

Question

Background:

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.

Question:

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.

• Erica: This Question might get you closer to what you need: Definition query to select the max value from a group. Most important note from that discussion: subqueries like this are not supported in a shapefile or file geodatabase, so you'd need to use a personal geodatabase (.mdb).
• hedz09: @Erica , thanks for that. With this in mind, are there other options to achieving my desired end result (only show the latest observation of each object) through some other means, while sticking with a File Geodatabase?
• Branco: I think a definition query would be a good idea, but I think you might have to do another query before that to get the features you wish to show. On desktop, you could simulate it by generating your query and then transferring the unique identifiers to the definition query. On the JS API, look into the feature layer with its query (developers.arcgis.com/javascript/jssamples/fl_paging.html), which may help get the unique identifiers which can then be put into a definition query for that layer there.