# Selecting top 10 records in attribute table using ArcMap

I am using ModelBuilder in ArcMap 10.2 to try and find a way to select the first 10 records sorted by Category and then by Distance. For example, my table has 100 banks and 100 churches, which is the "Category" field, and I have the distance from my site to each record stored in the "Distance" field. Essentially I want to be able to select the 10 closest banks and the 10 closest churches and export the selected records, using a tool in ModelBuilder.

Do you have any suggestions?

The SearchCursor syntax in the previous comments and posts is outdated and 10 times slower than a data access cursor if you have Desktop 10.1 or later. Only use DA cursors.

Here is my script, assuming your categories of banks and churches are in the same field in the feature class, this script will get the top ten items for all categories or only specified categories if you use that option. The end of the script will create a separate feature class of the top 10 items for all specified categories that will be sorted by category and then by distance. It performs at least 10 times faster than sorting a cursor.

print "Script Set Up"

import arcpy

# User defined variables
ws = r"C:\Path\OptionalGDB.gdb"

sourceFC = "Feature_Class"
outDataset = "Feature_Class_TOP_10s"

# substitute your Field names for the Category and Distance values
Category = "CATEGORY_FIELD"
Distance = "DISTANCE FIELD"

# Optionally remove comment from second line and modify to list specific categories
categorySet = ''
# categorySet = Category " IN ('BANKS', 'CHURCHES') AND "

### Scripted routine that should not need editing

# Set the workspace
arcpy.env.workspace = ws

# Make a feature layer for selecting records
sourceLayer = sourceFC + "Layer"
arcpy.MakeFeatureLayer_management(sourceFC, sourceLayer)

# Build a where clause
whereclause = categorySet + "OBJECTID > -1"

# select records based on the where clause
arcpy.SelectLayerByAttribute_management(sourceLayer, "NEW_SELECTION", whereclause)

# field list to be ordered by Category then Distance then OBJECTID
fields = [Category, Distance, "OID@"]

# Build a summary dictionary from a da SearchCursor for each category.
valueDict = {}
with arcpy.da.SearchCursor(sourceLayer, fields) as searchRows:
for searchRow in searchRows:
keyValue = searchRow[0]
if not keyValue in valueDict:
# assign a new Category to the dictionary storing a sortable string list
valueDict[keyValue] = ["%(Cat)s,%(Dist)031.15f,%(OID)020.0f" % {'Cat': searchRow[0], 'Dist' : searchRow[1], 'OID' : searchRow[2]}]
else:
# when a Category is already in the dictionary append to the list
valueDict[keyValue].append("%(Cat)s,%(Dist)031.15f,%(OID)020.0f" % {'Cat': searchRow[0], 'Dist' : searchRow[1], 'OID' : searchRow[2]})

# Build a where clause
whereclause = "OBJECTID IN ("

for item in sorted(valueDict.keys()):
i = 0
OIDS = ""
for OID in sorted(valueDict[item]):
OIDS += str(int(OID.split(",")[2])) + ","
whereclause += str(int(OID.split(",")[2])) + ","
i += 1
if i >= 10:
break
print str(OID.split(",")[0]) + " OBJECTIDs - " + OIDS[:-1]

print ""
whereclause = whereclause[:-1] + ")"
print whereclause

# select records based on the where clause
arcpy.SelectLayerByAttribute_management(sourceLayer, "NEW_SELECTION", whereclause)

### Optional section for creating a sorted feature class of top 10 items

### Delete the output table if it exists
if arcpy.Exists(outDataset):
arcpy.Delete_management(outDataset)

### Set Order of features to be first by Category and then by Distance
sort_fields = [[Category, "ASCENDING"], [Distance, "ASCENDING"]]

### Output Sorted Selected top 10 Features for all categories
arcpy.Sort_management(sourceLayer, outDataset, sort_fields)


If you want to process the top 10 items of each category separately then revise the end of the script to:

for item in sorted(valueDict.keys()):
i = 0
OIDS = ""
# Build a where clause
whereclause = "OBJECTID IN ("
for OID in sorted(valueDict[item]):
OIDS += str(int(OID.split(",")[2])) + ","
whereclause += str(int(OID.split(",")[2])) + ","
i += 1
if i >= 10:
break
print str(OID.split(",")[0]) + " OBJECTIDs - " + OIDS[:-1]
print ""
whereclause = whereclause[:-1] + ")"
print whereclause

# select records based on the where clause
arcpy.SelectLayerByAttribute_management(sourceLayer, "NEW_SELECTION", whereclause)

# Do something with the current category top 10 selection at this indent level

• This code worked very well for me. Again, a small-ish dataset (~10K records) but as I used it for a geoprocessing service, fast and light was essential for UI/UX. – JonR Sep 19 '17 at 1:08
• Ive been trying to figure out how to change this so instead of closest, it gives furthest. Cannot find where it sorts the values, any input? Thank you – Maksim Dec 13 '17 at 19:28
• The sorted function is used the first line of the second code block to sort the dictionary values in ascending order. To reverse the sort, change that line to - for item in sorted(valueDict.keys(), reverse=True): – Richard Fairhurst Dec 23 '17 at 13:59
• Thanks for this script. I'm a Python novice, so correction welcome, but I had to add another reverse=True a little further down in order to select by 10 largest values (as per @Maksim's query): for OID in sorted(valueDict[item], reverse=True): However, what I cannot fix is that the final output (as created by the last part of the original script) only shows the top 10 of the last category evaluated, not the top 10 of all categories. Any advice? – ermintrude75 May 28 '18 at 21:19

I think we can handle this using an arcpy.SearchCursor (I still use the Old School SearchCursor) and usage of the arcpy.Select_analysis() tool. The following is probably inefficient but I hope it helps. This assumes the Banks and Churches layer is held in a File Geodatbase (.gdb):

import arcpy
BanksAndChurches = r'Path\To\BanksAndChurches\FeatureClass'
arcpy.env.workspace = r'Path\To\OutputGDB'
closest_IDs = []


First, we need a search cursor to grab the top 10 closest banks and add their OID's to the list of desired output features:

cursor = arcpy.SearchCursor(BanksAndChurches, where_clause='Category = "Banks"', sort_fields="Distance D")
counter = 1
for row in cursor:
if counter <= 10:
closest_IDs.append(row.getValue("OID"))
counter += 1
del row
del cursor


Then do the same thing for Churches:

cursor = arcpy.SearchCursor(BanksAndChurches, where_clause='Category = "Churches"', sort_fields="Distance D")
counter = 1
for row in cursor:
if counter <= 10:
closest_IDs.append(row.getValue("OID"))
counter += 1
del row
del cursor


Then take this list of 20 OID's and use it to select out the closest 10 banks and closest 10 churches into an output file:

oid_list_as_string = ''
for item in closest_IDs:
oid_list_as_string += str(item)
oid_list_as_string += ','
oids = oid_list_as_string[:-1]
arcpy.Select_analysis(BanksAndChurches, env.workspace + "\\Top20Sites", "OID in(" + oids + ")")


Best Luck with this.

• This is 50 time slower than my code. I will never ever use the old style cursor again in my life and especially not a sorted old style cursor. – Richard Fairhurst Nov 21 '14 at 19:47
• True, da.SeachCursor() is faster. I'm sure Drew will be able to appreciate the 50x speed increase across his 200 records :-) – Jim Nov 21 '14 at 20:00
• I only build code that can scale to any size. My code works fine for 200 records or 2 million records (about 4 to 5 minutes), while your code could take hours to deal with 2 million records. – Richard Fairhurst Nov 21 '14 at 20:08
• I really appreciate the help from all of you guys. My first test run using Jim's code got that job done and speed wasn't too much of any issue since my max records count is about 5,000, but I will certainly try out the DA Cursor and try and condense my code a little bit based off the other suggestions. – Drew M Nov 22 '14 at 15:43

Here is a bare-bones example (borrowing from both Jim and Richard's answers):

import arcpy

fc = r'C:\junk\FILE_GDB.gdb\Export_output_gdb'  # input feature class
field1, field2 = 'Distance', 'Type'             # fields to sort, and group
fcOut = r'in_memory\blah'                       # output feature class

# set up counters
bankcount = 0
churchcount = 0

# start where clause
whereclause = "OBJECTID IN ("

# run through sorted
for row in sorted(arcpy.da.SearchCursor(fc, [field1, field2, 'OID@'])):
if row[1] == 'Bank':
if bankcount < 10:
bankcount = bankcount + 1           # increase counter
whereclause += str(row[2]) + ','    # add to where clause
if row[1] == 'Church':
if churchcount < 10:
churchcount = churchcount + 1       # increase counter
whereclause += str(row[2]) + ','    # add to where clause
del row

# finish where clause
whereclause = whereclause[:-1] + ")"

# make feature layer
fLayer = 'fLayer'
arcpy.MakeFeatureLayer_management(fc, fLayer)
# make selection
arcpy.SelectLayerByAttribute_management(fLayer, "NEW_SELECTION", whereclause)
# write to output
arcpy.CopyFeatures_management(fLayer, fcOut)

• I appreciate the code improvements you have made for this specific case, and agree that this bare bones code should perform well with 200 or 2 million records. However, I intentionally structured my code to deal with any number of unique categories in a field, since that is how I intend to use it with my own data. In reality I foresee the need to replace the where clause approach with a dictionary and/or a da insert cursor to have the script scale up to meet my data needs, since I work with 1,000s of unique category values normally, and a where clause with 10,000+ ObjectIDs is a bit large. – Richard Fairhurst Nov 22 '14 at 3:09