GROUP BY clause causing ArcGIS to crash

Question

I am attempting to find the most date for each occurrence in a given county. I am attempting to do so with a SQL expression, but every time I add a GROUP BY clause ArcGIS crashes (I am doing the work in Pro, but tested the same thing in ArcMap, and both crash immediately when attempting to run or verify an expression with GROUP BY)

SELECT * FROM [Table] WHERE is implied, so my code reads:

ALARM_DATE =
(SELECT MAX(ALARM_DATE)
FROM
CountyOccurrences_firep18_1_A_1000acres)
GROUP BY
COUNTY_NAME

Everything up to GROUP BY is valid, but of course it only returns the most recent date in the whole table. Whenever I add GROUP BY to organize by COUNTY_NAME, even if just verifying the expression, the software crashes immediately.

The data is stored in a local .gdb, and I have attempted it on the feature class as well as an exported stand-alone table.

I am looking for a workaround to achieve the objective of selecting the most recent occurrence by county.

Answer 1

As noted in @TravisB's answer, 'naked' SQL isn't an option. However, many SQL functions have been replicated in Esri's own Arcade code system. If you're working with the latest versions of their software (minimum version 10.8 for Enterprise), the Arcade function GroupBy has been included.

See the documentation here. The example Esri gives is as follows:

var treeStats = GroupBy($layer, 'TreeType', { name: 'NumTrees', expression: '1', statistic: 'COUNT' });

In your situation, it may look something like this:

var alarms_layer= FeatureSetByName($map, 'CountyOccurrences_firep18_1_A_1000acres', ['COUNTY_NAME', 'ALARM_DATE'], false)

var latest_by_county = GroupBy(alarms_layer, 'COUNTY_NAME', { name: 'most_recent_alarm', expression: 'ALARM_DATE', statistic: 'MAX' });

It outputs a FeatureSet, which depending on how you're using the output, can be interacted with further to populate a field or popup text. I've used the function myself to aggregate data from other fields, and even other layers entirely, both in the field calculator and in popups.

Of course, you may not want to learn or use Esri's own coding system and prefer to stick with SQL or python as the situation warrants. Arcade does have the advantage of being cross-compatible with all of Esri's web-based tools (it is, in fact, the only kind of coding available in the AGOL / Enterprise web map environment), as well as the latest iterations of Field Calculator, Attribute Rules, etc. It's similar-ish to JavaScript, if that matters to you.

Answer 2

Answer:

Re: GROUP BY: I posted on GeoNet as well and Esri folks said it was essentially impossible to use 'naked' SQL in queries like that. Why it takes the program down nobody knows, but I learned that that's not a possible solution.

Per @Kirk Kuykendall 's advice, I used the summary statistics tool, using the counties as the case field, and then joined back to create a new layer.