Select to view content in your preferred language

Selecting max value in SQL query by group in ARCMAP

2218
4
04-10-2022 03:17 PM
Labels (3)
khalidechlouchi
Occasional Contributor

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.
IDVALUE
15
12
110
23
24
26
316
320
34

 

 

0 Kudos
4 Replies
JeffHouser
Frequent Contributor

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)

To 

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

-Jeff

0 Kudos
khalidechlouchi
Occasional Contributor

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

0 Kudos
DuncanHornby
MVP Notable Contributor

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:

DuncanHornby_0-1649635467306.png

 

khalidechlouchi
Occasional Contributor

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.