I'm using the following code in my ArcMap python window and it's throwing an error. I'm trying to sort by two fields, one ascending and the other descending. The error seems to be indicating a typo but the field names are correct. This is referencing a layer in the table of contents from a file geodatabase feature class.

>>> with arcpy.da.UpdateCursor ("Parcels_Intersecting_ServiceLocations", ["Parcel_Ranking"], None, None, False, (None, "ORDER BY PGA_Plus_LIQ DESC, Fault_Dist_Meters ASC")) as curs:
...     q = 0
...     for row in curs:
...         q += 1
...         row [0] = q
...         curs.updateRow (row)

The error:

Runtime error 
Traceback (most recent call last):
  File "<string>", line 3, in <module>
RuntimeError: An invalid SQL statement was used. [SELECT Parcel_Ranking, Shape_Area, Shape_Length FROM Parcels_Intersecting_ServiceLocations ORDER BY PGA_Plus_LIQ DESC, Fault_Dist_Meters ASC]

I did some sanity checks:

>>> arcpy.Exists ("Parcels_Intersecting_ServiceLocations")
>>> flds = [f.name for f in arcpy.ListFields ("Parcels_Intersecting_ServiceLocations")]
>>> print [f for f in ["Parcel_Ranking", "PGA_Plus_LIQ", "Fault_Dist_Meters"] if not f in flds]

Am I missing something obvious here?

  • I thought that ORDER BY only worked with enterprise databases, not file geodatabase? – Midavalo Oct 19 '17 at 20:17
  • 1
    @Midavalo from the help: DISTINCT, ORDER BY, and ALL are only supported when working with databases. They are not supported by other data sources (such as dBASE or INFO tables). I've used it with file geodatabases in the past. – Emil Brundage Oct 19 '17 at 20:20
  • Do field names in FGDB need to be enclosed in square brackets for the SQL statement portion? e.g. ORDER BY [PGA_Plus_LIQ DESC], [Fault_Dist_Meters] ASC? (I am obviously just taking guesses here!) – Midavalo Oct 19 '17 at 20:47
  • @Midavalo See example 5B for file geodatabase sql_clause pro.arcgis.com/en/pro-app/arcpy/data-access/… – Emil Brundage Oct 19 '17 at 21:16
  • 3
    @Vince why do you say that? I'm creating a ranking based on sorting two fields. – Emil Brundage Oct 20 '17 at 0:07

Use sort_management to sort fields then call it in the cursor

Example :

sorted_table = arcpy.Sort_management(layer,"in_memory/sorted_pop","POP2010 DESCENDING")
cursor_sorted_table = arcpy.da.SearchCursor(sorted_table, fields)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.