[GIS] Selecting maximum value in attribute table using Python code

arcpyqueryselect

I saw a lot of posts about how you select maximum value in the particular field and I've been trying to use that query in a python code, but none of the posts really helped me solve my problem. There is a problem that I always get an error saying that I have an invalid expression.

I've been using:

arcpy.Select_analysis (input_layer, output_layer,"Shape_Area = (SELECT MAX( Shape_Area)" FROM layer_name)

I'm so confused as this query sometimes works and sometimes not, even in the attribute table or if I'm trying to use a tool Select, Table Select etc.
Can anyone help me? Or is it there some other way how to identify the maximum value in the field.

Best Answer

Have you seen SQL reference for query expressions used in ArcGIS, subqueries section?. Depending on your data not all queries are supported:

Coverages, shapefiles, and other nongeodatabase file-based data sources do not support subqueries. Subqueries that are performed on versioned enterprise feature classes and tables will not return features that are stored in the delta tables. File geodatabases provide the limited support for subqueries explained in this section, while enterprise geodatabases provide full support. For information on the full set of subquery capabilities of enterprise geodatabases, refer to your DBMS documentation.

It could explain why it sometimes works for you if you have different inputs.

You can do it without using ArcGIS selection tools if that is an option for you. This will find max value in a specified column and print out ObjectID:

import arcpy, operator
fc = r'C:\Default.gdb\featureclass123' #Change to match your data

#Build a dictionary of objectids and values:
all_entries = {key:value for (key, value) in arcpy.da.SearchCursor(fc,['OID@','VALUECOLUMN'])} #Change VALUECOLUMN to the name of your column

#Find max value and print out objectid
print max(all_entries.iteritems(), key=operator.itemgetter(1))[0]

The objectid can of course be passed on to Select By Attributes if you want to select the row:

arcpy.MakeFeatureLayer_management(in_features=fc, out_layer='fc_lyr')
sql = '{0} = {1}'.format(arcpy.AddFieldDelimiters(datasource=fc, field=arcpy.Describe(fc).OIDFieldName), max(all_entries.iteritems(), key=operator.itemgetter(1))[0]) 
arcpy.SelectLayerByAttribute_management(in_layer_or_view='fc_lyr', where_clause=sql)
Related Question