Create a Definition Query that Shows Only Most Recent Record

ChristopherBowering

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.

Laura

I just did this for my inspections relationship!

Use this to create database views of your table. https://community.esri.com/t5/arcgis-survey123-questions/select-display-most-recent-record/m-p/10403...

I set mine up by 'inspection date'. Each hydrant is inspected multiple times but this just returns the most recent inspection per hydrant.

ChristopherBowering

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.

RhettZufelt

This should work for definition query, use your fields:

created_date IN (SELECT TOP 1 created_date FROM FeatureclassName ORDER BY created_date DESC)

R_

ChristopherBowering

I did try this. However, it only shows 1 singular record as a result.

I want to be able to see the most current record amongst multiple with the same ID. For example, GUID of XYZ only has one record; GUID of YYY has 5 records; GUID of ZZZ has 2 records, etc. It's a 1-to-many table. For each GUID value, I only want to see the most current record.

JoshuaBixby

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

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