GROUP BY clause crashes pro?

[SFM_TravisBott]

Issue with the GROUP BY clause using ArcGIS PRO 2.4.2 I have a layer and I am attempting to find the most recent occurrence of a phenomenon in each county. I have attempted to use a SQL expression in the Select by Attributes tool. Since SELECT * FROM

WHERE is implied, I have entered...

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

Everything above GROUP BY is valid, but when used on its own just returns the most recent observations in the table. I would like the most recent observations from each county,  hence the GROUP BY. 

However, whenever I add the GROUP BY clause, and attempt to either run it or validate, Pro immediately crashes. Looking for feedback on what's going on, as well as a workaround to perform the selection I want. 

Thanks

[MarcoBoeringa · Accepted solution]

The crash is obviously a bug in ArcGIS Pro, that may or may not be fixed in the more recent V2.5 version of Pro, but alternatively may need reporting to ESRI.

As to your specific query: This type of advanced query including GROUP BY doesn't work well directly in dialogs or tools like Select By Attribute.

Is this data stored in an enterprise (geo)database? If so, you have several options:

- Define a database view that creates the desired output, and load that through the Catalog window (You can use the Create Database View—Data Management toolbox | Documentation ) tool for creating the view, or database tools).

- Use ArcGIS Query Layers to define the SQL and create a layer. See e.g.:

What is a query layer?—Query layers | ArcGIS Desktop 

Create a query layer—Query layers | ArcGIS Desktop 

Lastly, have you looked at the Summary Statistics—Help | Documentation tool as well? It may be able to give you the desired output with the right settings for fields and a join back to the original layer.

[JoshuaBixby]

I agree with Marco Boeringa‌ in that there is obviously a Pro defect because the application should not crash, even with bad syntax.

Speaking of bad syntax, your SQL is definitely not valid.  Something that is valid but still doesn't guarantee correct results is:

ALARM_DATE IN (
  SELECT MAX(ALARM_DATE) 
  FROM CountyOccurrences_firep18_1_A_1000acres
  GROUP BY COUNTY_NAME
)‍‍‍‍‍

To guarantee correct results, you would need to use a correlated subquery, which unfortunately Esri does not properly support with file geodatabases.  You can read /blogs/tilting/2016/10/04/when-exists-doesnt-file-geodatabases-and-correlated-subqueries for some more background and examples.

[SFM_TravisBott]

Thanks for all of the responses. 

I got around it by using the Summary Statistics tool and then joining back to the original data set. Wasn't perfect, due to the nature of the dataset, but with some manual winnowing was able to get what I needed. 

The query that I posted in my OP, minus the GROUP BY, definitely did work, just not as intended. However I will take note that the GROUP BY clause should exist within the parenthesis. 

[JoshuaBixby]

Since you have identified the root cause and a new workflow, please mark a response as correct or the thread as assumed answered to close it out.

[KoryKramer]

SFM Travis Bott‌  Sorry that you experienced a crash, but glad that you're moving forward.

If you send us (kkramer@esri.com) the .dmp files from the associated crash we can get them to the development team to analyze - we have the description of your query from this thread.  Go to C:\Users\<username>\AppData\Local\ESRI\ErrorReports and attach a few of the latest .dmp files from Pro crashing.  The filename contains a date-time stamp:

Thank you!