1

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)

There's a sample of the table below.

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.

8
  • Found a more appropriate expression: ``` ALARM_DATE = (SELECT MAX(ALARM_DATE) FROM CountyOccurrences_firep18_1_A_1000acres GROUP BY COUNTY_NAME ``` However, the GROUP BY clause seems to crash Pro. The I can isolate what's prior and get the most recent date in the whole table, but attempting to do so by county (even just attempting to validate the expression) takes it down. Any suggestions on a workaround would be appreciated.
    – TravisB
    Apr 23, 2020 at 19:10
  • group by should be legal when used in a query layer ... have you tried that? Apr 23, 2020 at 19:14
  • I have not, but the data is stored locally, not in a database.
    – TravisB
    Apr 23, 2020 at 19:53
  • 2
    If you're ok with creating a new dataset, have you tried using the Summary gp tool with County as the case_field, with max alarm_date as a statistic field? pro.arcgis.com/en/pro-app/tool-reference/analysis/… Apr 23, 2020 at 20:05
  • 2
    I think @Kirk_Kuykendall idea of the summary tool is the way to go..
    – Hornbydd
    Apr 23, 2020 at 23:22

2 Answers 2

2

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.

1
  • this is very helpful, thank you. I found a workaround for the situation originally posted about, but it is good to know this functionality exists in Arcade.
    – TravisB
    Jul 14, 2020 at 15:24
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.