Select Lowest Values using Arcpy?

Question

I have a table (Feature class in FGDB) with 2 columns, one is the "name" and the other is a list of year values as text "Value1".

How do I obtain the lowest 5 values for EACH name?

I am trying to do this with arcpy or even select by attributes however I am lacking SQL skills to make this happen as expected.I tried this with arcpy however it comes down to SQL for this statement. I tried MIN(date) and that is only for one value.

arcpy.SelectbyAttribute_management(r"<my path>"), "New_Selection", ' [Field1] = 'lowest5' AND GROUPBY [NAME] 

• Michael Stimson: Can you give us a screen shot of the table? What sort of feature class/table is it? Is it shapefile, personal GDB, file GDB etc.. can you show us where you're up to with arcpy?
• Robert C: I can't do a screenshot for security reasons, however it is in a FGDB.
• Michael Stimson: File geodatabases aren't real databases, they don't support the full SQL. If you're ok with python I would use a search cursor to get a list of unique names, then select by each unique name and search cursor again to get the year values as a list, sort the list smallest to largest and return the 5th value in the list to define the where clause for each unique name... where do you need to go from here?
• PolyGeo: There should be no need for SQL to do this. Try Summary Statistics to group and order, and cursors to read first 5 values in each group in the output table.
• Vince: Yup, this is an exercise for Summary Statistics

Answer 1

This is one way to do it:

def SelectLowestN(layer,n,groupfield,valuefield):
 import operator
 list1 = sorted([i for i in arcpy.da.SearchCursor(layer,[groupfield,'OID@',valuefield])],key=operator.itemgetter(0,2))
 oids=[]
 d={}
 for item in list1:
 key=item[0]
 if key not in d:
 d[key]=[]
 d[key].append(item[1])
 for key in d:
 oids+=d[key][:n]
 sql="{0} IN ({1})".format(arcpy.AddFieldDelimiters(layer,"OBJECTID"),", ".join(map(str,oids)))
 arcpy.SelectLayerByAttribute_management(in_layer_or_view=layer, 
 where_clause=sql)

Call the function:

SelectLowestN("Points111",5,"Name","Year_int")

Result:

enter image description here