4
fc = "C:\Users\AuxS3-4\Documents\ArcGIS/Default.gdb\Malla" 
    fields = ('OBJECTID','Sentido','Carriles','idMalla') 
    sqlclause=(None, "GROUP BY Sentido")
cursor = arcpy.da.SearchCursor(fc, fields,sql_clause=sqlclause)

not working

RuntimeError: An invalid SQL statement was used. [SELECT OBJECTID, Sentido, idMalla FROM Malla GROUP BY Sentido

arcgis 10.1!!!

  • 2
    I have tested this and found the same problem at 10.2 with a file geodatabase. I tried with and without enclosing the field name in quotes. The GROUP BY clause is supposed to be supported at 10.1 and up in file geodatabases. – blah238 Oct 2 '13 at 23:52
  • 2
    Looks like another thread on this topic. I was testing locally and also had trouble: gis.stackexchange.com/questions/67810/… – bcollins Oct 3 '13 at 4:30
2

First, you can't include more fields than those that are being grouped unless the other fields are being used in an aggregation (Sum, Count, Max, Min, etc.). This isn't a limitation of ArcGIS, but of SQL.

In other words, for every value of Sentido, there will be several values for OBJECTID, Carriles and idMalla. How can it group by Sentido and return values for the other fields at the same time?

Secondly, and most important I suppose, this appears to be a bug (in 10.1 at least), or we are all misunderstanding how to use the sql_clause parameter of arcpy.da.SearchCursor. I've tried several combinations of using a GROUP BY clause with a search cursor and nothing will work. It keeps including the ObjectID field even when not specified in the fields parameter, which causes an error due to the above.

So, even when you do get your fields formatted properly, I don't think it will work as expected. I recommend contacting ESRI about this.

| improve this answer | |
  • It depends on the implementation whether an aggregation function is required, but from my understanding, it should not be required and should simply return an indeterminate (random, or perhaps first) value for other columns in the grouped rows. – blah238 Oct 3 '13 at 17:11
  • Although I've never encountered a database that supports the behavior you are describing, it is possible. That would be undefined behavior though, and I would never recommend designing any process, code or script around it. – Evil Genius Oct 4 '13 at 13:48
  • I just know that MySQL works that way, not sure about others. – blah238 Oct 4 '13 at 16:07

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.