Select By Attributes latest date from Data Table

[MatthewReynolds]

Ok,  I'm having issues with what seems like a simple task.  I 'm trying to select the latest record from multiple facilities in a Table.  For example... the latest inspection record for City electrical transformers.  Ive tried the Sort Tool and Summarize tool - both are not working within the Model I'm building.  I was excited to see the expression for the select  by attributes tool below.  If anyone knows how to get this expression to work in Arc10, I would really be interested to hear how!  Ultimately I'll create a Feature Class Layer from the selection.


[mydate] in (SELECT max( [mydate] ) FROM table_name GROUP BY [groupID])

Regards.

[RichardFairhurst]

Ok,  I'm having issues with what seems like a simple task.  I 'm trying to select the latest record from multiple facilities in a Table.  For example... the latest inspection record for City electrical transformers.  Ive tried the Sort Tool and Summarize tool - both are not working within the Model I'm building.  I was excited to see the expression for the select  by attributes tool below.  If anyone knows how to get this expression to work in Arc10, I would really be interested to hear how!  Ultimately I'll create a Feature Class Layer from the selection.


[mydate] in (SELECT max( [mydate] ) FROM table_name GROUP BY [groupID])

Regards.

It is not supported.  Subqueries cannot preserve groups and queries have to be to an external table, not the table itself.  So if you could get the maximum date for an external table, it would be a single date for the entire table, not several dates based on groupings by another field.  The help for subqueries says this about summery queries, which are called scalar subqueries:

Scalar subqueries with comparison operators. A scalar subquery returns a single value.

I also do not know if MAX works with dates anyway, with the reworking of SQL 92 done by ESRI.  I think only First works with dates.  Max may be limited to Numeric fields.  I have asked the person who programs the SQL underlying file geodatabases to rewrite the SQL reference to show what SQL 92 methods are supported, and he told me he is working on it, but the subquery limitations will most likely remain.

I agree it is a pain that you cannot do summary statistics on a date.

There are two work arounds.  You can convert your date to a numberic field in the form "YYYYMMDD" and get the maximum value by group for that number using the Summary Statistics tool.  Using the Field calculator you can convert the number back to a date using python or VB Script parsing and/or formatting to do real date logic calculations.  A terrible work around, but fewest steps.

The other way involves more steps but it would preserves the actual date values:

1. Use the Sort tool and sort by GroupID and Date descending.  Subsort on any other fields to make them be the first record in the GroupID and Date set if you want a specific record (like the maximum UniqueID for a given GroupID and Date). I will call the output SortTable.

2. Summarize by GroupID again, but this time get the Min value of the ObjectID field for each GroupID.  I will call the output table SummaryTable

3.  Add and Attribute index to the MIN_OBJECTID field.

4  Join that output to the previous summary on the ObecjtID and MIN_ObjectID and Select records where:

NOT SummaryTable.MIN_OBJECTID_Min IS NULL

5.  Break the join.

6.  Export the selection to a new table.

7.  Add an Attribute Index on the exported table's GroupID, Date, and any other field you want to use in a join or select.  You now have a table containing the latest date for every GroupId in your original data which can be joined back to the original data to do date selections or calculations.

I hope this helps.

(Note, ArcMap crashed when I attempted the selection the way I originally wrote this post.  So I rewrote the join and SQL and added a step to get create an Attribute Index on each table.

[RichardFairhurst]

I have posted an idea to improve the ability to find Min and Max Dates and perform subqueries in fgdb's here.  Please vote for it and offer comments and further suggestions if you need better support for analyzing dates in ArcMap.

[RachelParrinello]

Good suggestion - I would like to vote for your idea for improved ability to find Min and Max Dates and better perform subqueries in ESRI software, but when I click on your link to vote, it takes me to a dead link (sales force website).  Do you know more about how to vote for this, or any recent developments?  Thanks, Rachel P

[RichardFairhurst]

I have edited the link in my post to be current to the url that appears in my browser when I pull up that idea on the Ideas site.  Try it again.  Or else just go to Esri Arcgis Ideas | Ideas Submission Portal and search for

Expand Summary operations to include Min or Max Dates and fgdb Subqueries

[RichardFairhurst]

Actually it appears fgdb subqueries can return Max date values, but they cannot do a correlated subquery that applies the Group By option.  If the original query had worked it would only have returned all records with the dates that matched the list of dates associated with each unique ID value.  But the query would not have preserved the group correlation in the selection output, since the original syntax only correlates the dates to the ID groups to create the date list, not for doing the selection of records based on those dates.  An external table is needed and a join is the only way to do this.

[JustinWolff]

I know this post is old, but I'm struggling to do what is simply the GROUP BY function in SQL.

I have a table (in a file geodatabase) of land parcels with sale dates - multiple rows in the table have the same parcel number but different sale dates - I need a query for records with the latest/most recent sale date).  For various reasons I need to use File Geodatabase.

Can Model Builder in Pro 2.6.3 handle this without the need for additional tables, etc.?  Thanks