AnsweredAssumed Answered

sql_clause in  arcpy.da.SearchCursor is NOT honoring "ORDER BY"

Question asked by wesleygarrett_usfs on Aug 7, 2020
Latest reply on Aug 9, 2020 by wesleygarrett_usfs

I am working on a script that is attempting to use ORDER BY. The results I'm seeing are unordered. I've dug quite a bit in GeoNet and elsewhere to no avail. My data is in a FGBD, my understanding is ORDER BY should work... 

 

I'm hoping that there is something wrong with the logic in my script, and someone might help me identify it. 

 

In a nutshell the script selects a group of features based on FEAT_SEQ, the selection is then sorted by a RANK field (NOT WORKING), the first row is then written to a new feature class. All of this is working except for the ordering. The output simply writes the first row, unsorted. 

 

  • The TableSelect, selects features in a FC by the FEAT_SEQ field. The FEAT_SEQ is a result of the identify duplicate tool. 
  • This selection is intended to be "sorted" by the ORDER BY using the SearchCursor
  • Finally the first row is written to a new FC 

 

Seq_Count = 0 #counter for FEAT_SEQ selection
for i in range(5): #loop - range 7631 (all)? for full run
        Seq_Count = Seq_Count + 1 # adds 1 to Seq_Count
        Where = "FEAT_SEQ =" + str(Seq_Count) #sets sql expreassion to Seq_Count #
        print("Looping Seq #" + str(Seq_Count)) # prints Seq_Count # for ref.
        arcpy.TableSelect_analysis(DUPES, Out, Where) # selects
        #print("Selection Complete - saved to cleanscript.gdb/SelSection") # loop done
        ### load selection from previous output, evalute winner, save winner to winnigselection
        with arcpy.da.SearchCursor(Out, field_names = fieldnames, sql_clause = (None, 'ORDER BY RANK')) as searchCursor: #orders search by ranked
                with arcpy.da.InsertCursor(winnerTable, fieldnames) as iCur:
                                        row = next(searchCursor) #goes to first row (previouly ordered by rank)
                                        #print(row)
                                        iCur.insertRow(row)
                                        #print("insert complete")
                #del iCur #seems like you do this for housekeeping
        #del searchCursor #seems like you do this for housekeeping
result = arcpy.GetCount_management(winnerTable) #counts output to winnerTable
print('{} has {} records'.format(winnerTable, result[0])) #prints count

 

The sql_clause is on line 9 (shown below) 

 

with arcpy.da.SearchCursor(Out, field_names = fieldnames, sql_clause = (None, 'ORDER BY RANK')) as searchCursor:

 

screenshot of same code (in case it is easier to read)

Same code from notepad++ if anyone prefers to view it tis way

 

Thanks in advance for any insights anyone has to offer!

Outcomes