Using Select Layer By Attribute to return top 3 records

Question

I have a feature class in a file geodatabase using ArcGIS 10.1. I want to create a model that selects the three features with the highest values in one field (Average Price). Basically I want my selection to include the three features with the highest average prices.

How do I accomplish this?

Answer 1

I would just do something like this:

field = "AveragePrice"

values = [row[0] for row in arcpy.da.SearchCursor(fc, field)]
top_three = sorted(values)[-3:]

query = "\"{0}\" in {1}".format(field, tuple(top_three))

arcpy.MakeFeatureLayer_management(fc, "top_3_layer", query)

Answer 2

One way to solve this would be to use a sql clause in a arcpy.da.SearchCursor to sort your values in descending order. Then you would store the third value of the sorted column. The trick is that you only want to get the third highest so you can utilize a counter variable. Without testing the code something like this could get the job done:

searchFields = ‘AveragePrice’
query = ‘AveragePrice’
sqlClause = (None, ‘ORDER BY ‘ + query +  ‘ DESC’)
ct = 0
with arcpy.da.SearchCursor(fc, field_names = searchFields, sql_clause = sqlClause) as searchCursor:
    for row in searchCursor:
        if ct == 3:
            break
        elif ct == 2:
            thirdHighestPrice = row[0]
        else:
            ct += 1
            continue

Then feed that value of thirdHighestPrice into a arcpy.SelectLayerByAttribute_management statement to select all values that are greater than or equal to the variable thirdHighestPrice.

Answer 3

Its not going to be a eloquent solution, but using "Summary Statistics" tool, you could get the MAX value of an attribute. Then do a join using that MAX value to select from your FC that record, remove that record from your original FC and put it aside (Switch Selection). Repeat three times and merge the outputs.