Create a Definition Query that Shows Only Most Recent Record

Question

I have a table where some inspections records have the same ID (associated with the same parent feature) but have different dates based on when inspections were done.  I would like to create a query which only shows the most recent record when multiple records with the same ID exist.  There is obviously nothing 'out of the box' for this so I imagine a custom SQL definition query will be necessary.  I am not too familiar with scripting outside of basic Arcade.

ChristopherBowering · Answer

The context is visualization.  Ultimately, I would publish this table to a web service and use in a WebApp. Users could view the table and only see the most current record when there are multiple tied to a parent feature.

@JoshuaBixby

JoshuaBixby · Answer

What is the context?  Is this for labeling, visualization, analysis, etc...?  Knowing the context can open the door to context-specific options.  Regarding SQL, it is complicated because SQL support depends on the back-end data source, and the more advanced the SQL the less standard the support becomes.  That is, SQL isn't always very portable as a solution.

ChristopherBowering · Answer

I was hoping for a more simplistic approach than creating views or involving the relationship.  Maybe there isn't one, however.  I don't really care about the parent feature class itself.  If you think of it as a basic standalone table - say, with Field X and a Date field - and you just want a definition query to see the most recent record for each Field X value that exists.  That's what I'm going for.