topic Re: Selecting max value in SQL query by group in ARCMAP in ArcMap Questions

Selecting max value in SQL query by group in ARCMAP

khalidechlouchi — Sun, 10 Apr 2022 22:17:34 GMT

HI, I am working with a feature class stored in a File Geodatabase In ArcGIS 10, . I want select the maximum value by ID like the example below:

I tried the following sql query but without result : VALUE in (SELECT max(VALUE) FROM featureClass GROUP BY ID)

How can I solve?

Thanks.

ID	VALUE
1	5
1	2
1	10
2	3
2	4
2	6
3	16
3	20
3	4

Re: Selecting max value in SQL query by group in ARCMAP

JeffHouser — Sun, 10 Apr 2022 23:56:36 GMT

Hello,

Just to confirm, your values in the above table are left-indented, suggesting text. Are your values text or numeric values. Numeric values usually displayed w/ right-indent.

Regarding your sql, maybe change your sql from:

VALUE in (SELECT max(VALUE) FROM featureClass GROUP BY ID)

[Value] IN (SELECT MAX( [Value] ) FROM featureClass GROUP BY [ID] ).

-Jeff

Re: Selecting max value in SQL query by group in ARCMAP

DuncanHornby — Mon, 11 Apr 2022 00:05:38 GMT

I don't believe File GeoDatabases support GROUP BY when selecting rows in a table? A quick search of this forum and GIS SE and you see many threads about the issue.

If you want to create a table of just that data (which is different to selecting rows) then you can use the summary statistics tool as shown below:

Re: Selecting max value in SQL query by group in ARCMAP

khalidechlouchi — Mon, 11 Apr 2022 01:11:32 GMT

Thank you for your feedback, the values are all numeric, I tried your solution, but it didn't work.

Re: Selecting max value in SQL query by group in ARCMAP

khalidechlouchi — Mon, 11 Apr 2022 01:30:53 GMT

Thank you DuncanHornby, you are right, GROUP BY is not supported in geodatabases file and shape files, I think I will try to do it on personal geodatabase with ACCESS.