Select by Attributes minimum value

[AndrewBlakey1]

Hello:

I have a Feature Layer of points. From this feature layer, I want to select only one point: the one with the minimum Elevation.

In ArcGIS I got it working with:

"Elevation "= (SELECT MAX("Elevation") FROM allpoints)

Now I need to transpose this to Python. I'm trying to figure out something like this:

pointMinClause = '"Elevation "= (SELECT MIN("Elevation") FROM  ????
arcpy.SelectLayerByAttribute_management (inputLayer,'NEW_SELECTION',pointMinClause)

I'm not sure how to reference the data in the same way that I do it in ArcGIS Desktop.

I also may be doing the rest of that query improperly for arcpy; I'm not sure.

Thanks!

[JakeSkinner]

Hi Andrew,

You can append the elevation values to a list, sort the list, and then use the first value in the list to obtain the minimum elevation.  Ex:

list = []

fields = ["Elevation"]
with arcpy.da.SearchCursor (inputLyr, fields) as cursor:
    for row in cursor:
        list.append(row[0])

list.sort()

pointMinClause = "Elevation = " + str(list[0])
arcpy.SelectLayerByAttribute_management(inputLyr, "NEW_SELECTION", pointMinClause)

[ChrisSnyder]

Per your question of using a subquery to do this. Something like this works to select record with the lowest (minimum) OBJECTID value in a table called 'sof'.

"OBJECTID" = (SELECT MIN("OBJECTID") FROM sof)

If you are dealing with a layer or table view that already has a query on it, you would have to include that existing query in the subquery as well.

Some data formats (like Shapefiles for example) don't support subqueries and in that case you would have to use something like this:

#Selects the record(s) that have the minimum value (assuming fieldName is numeric)
fieldName = "Elevation"
elevList = [r[0] for r in arcpy.da.SearchCursor (inputLyr, [fieldName])]
sqlExp = fieldName + " = " + str(min(elevList))
arcpy.SelectLayerByAttribute_management(inputLyr, "NEW_SELECTION", sqlExp)