arcpy.da.SearchCursor - sqlprefix

[ManikMohandas]

Does the sql prefix - DISTINCT work well on SDE tables.

http://resources.arcgis.com/en/help/main/10.1/index.html#//018w00000011000000

Syntax SearchCursor (in_table, field_names, {where_clause}, {spatial_reference}, {explode_to_points}, {sql_clause})

An SQL prefix clause is positioned in the first position and will be inserted between the SELECT keyword and the SELECT COLUMN LIST. The SQL prefix clause is most commonly used for clauses such as DISTINCT or ALL.

My following example is resulting in all rows, not distinct rows.  rs = arcpy.da.SearchCursor(sTableName, (sSelectField1, sSelectField2), sSqlExp, None, None, ("DISTINCT", sSqlPostfix))

1. What elese could I do to get truely distinct records only.
2. I store the values in a list and are they any function to get unique values in a list. Example [1,2,2,3,3,3,5,6,7] to [1,2,3,5,6,7]. Also set(xyz) won't work because I'm using a list, whereas set(xyz) works on tuples.

[ManikMohandas]

Wowww. Does "DISTINCT" work for you? If so what version of ArcMap are you using?

The whole problem started since use sqlprefix didn't work with arcpy.da.SearchCursor. It won't throw an error but wouldn't give the correct distinct recrods. So what do you mean by "Speed field is a test type and not numeric". Does python have a data type "test".  Just to rant, python could use better terminology for naming their data types - arrays(can they hold strings?), list, set, dictionary, tuples...... Ofcourse, I'm not an expert but its totally confusing.

[JamesCrandall]

It works for Robert because he is only returning 1 field.  Your OP has multiple fields -- how can you return distinct values for multiple fields without correctly apply a GROUP BY clause?

[CarstenBøcker1]

I have used arcpy.ArcSDESQLExecute with great succes when it comes to complex queries involving DISTINCT and GROUP BY. It also seems to perform pretty well as an alternative to using a cursor and doing the grouping in the loop.

Carsten

[JamesCrandall]

Can you post an example?  I remember not being able to correctly apply a GROUP BY with multiple fields present in the SQL statement but it has been a while since then and I am not sure if I can even locate the thread in this new place.

[JoshuaBixby]

At least in 10.2.2 with personal and file geodatabases, the following works for me:

with arcpy.da.SearchCursor(fc, ["field1", "field2"], sql_clause=(None, "GROUP BY field1, field2")) as cursor:
    for row in cursor:

[JamesCrandall]

Joshua,

I get a RuntimeError: An invalid SQL statement was used

Running this against a table in the default.gdb in ArcGIS 10.1:

tab = r'H:\Documents\ArcGIS\Default.gdb\arlist'
with arcpy.da.SearchCursor(tab, ["fc", "item2"], sql_clause=(None, "GROUP BY fc, item2")) as cursor:
    for row in cursor:
        print "{0}, {1}".format(row[0], row[1])

This work though (minus the sql_clause):

tab = r'H:\Documents\ArcGIS\Default.gdb\arlist'
with arcpy.da.SearchCursor(tab, ["fc", "item2"]) as cursor: 
   for row in cursor:
        print "{0}, {1}".format(row[0], row[1])

[JoshuaBixby]

With 10.1 I seem to remember some quirky behavior, I would argue a bug, where you need to include the OBJECTID field in the GROUP BY clause even if you aren't selecting OBJECTID.  Try the following and see if it works:

tab = r'H:\Documents\ArcGIS\Default.gdb\arlist' 
with arcpy.da.SearchCursor(tab, ["fc", "item2"], sql_clause=(None, "GROUP BY fc, item2, objectid")) as cursor: 
    for row in cursor: 
        print "{0}, {1}".format(row[0], row[1])

I believe the root cause was that OBJECTID in 10.1 is being implicitly selected with the SearchCursor and not handling it in the GROUP BY clause creates the error.  I don't recall this being listed in Addressed Issues for 10.2/10.2.1/10.2.2, but the behavior did change between 10.1 and 10.2.x.

[JamesCrandall]

Yes that gets past th error (and my memory of this issue).  It also reminds me that, again, it doesn't actually GROUP anything.

[JoshuaBixby]

Right, I forgot about that second part, which is why I wasn't using it in 10.1.  I know it is no consolation, but it does work in 10.2.2 once you can upgrade.

[RobertBurke]

Sorry Manny, Test should be Text.

I have a field to store speed limit values, 45, 55, 65 etc.  I was just trying to point out, that my field called 'Speed' is defined as the Text data type, and not as a Numeric data type as some might expect.

And I also only have the one field.  I haven't tried Two.

Thanks,

Rob