topic Re: Select Max value arcpy in Python Questions

Select Max value arcpy

timdunlevie — Mon, 14 Dec 2015 05:48:38 GMT

Hi all,

I have a FC in an SDE with Fields: ID & Version.

These are populated like this:

ID "A", Version "1"

ID "A", Version "2"

ID "B", Version "1"

ID "B", Version "2"

etc..

so when the ID object changes we create a new shape with a new Version number, but keep the old shape for historic purposes.

I am doing a join on another table but only want to perform the join on a subset of the original FC, grabbing the latest "Version" of the ID.

For the life of me I cannot seem to be able to select these records.

This will be a python script.

I was thinking of using the arcpy.MakeFeatureLayer_management with an expression and/or arcpy.SelectLayerByAttribute_management function with an expression to form my subset before I join to another table.

"Version" = (SELECT MAX("Version") from FC) - does not seem to work. I also tried a "group by" and got a sql error.

We are using SQL server and ArcGIS 10.2

Even in ArcMap I cannot seem to be able to select the latest version of the ID....I am guessing I don't have the syntax correct.

any help would be appreciated.

thanks

Re: Select Max value arcpy

DanPatterson_Retired — Mon, 14 Dec 2015 09:42:01 GMT

error message?

Re: Select Max value arcpy

ChrisSmith7 — Mon, 14 Dec 2015 19:26:45 GMT

Advanced editor works here!

Re: Select Max value arcpy

JoshuaBixby — Sat, 11 Dec 2021 22:10:07 GMT

I am guessing you are building your where clause incorrectly, but I can't say for sure unless you post the specific code and error message(s).

If the maximum Version number is the same across all IDs at any given time, then it simplifies the situation and using a basic subquery with MAX will work:

>>> lyr_name = 'tmpLayer'
>>> fc_name = 'tmpFC'
>>> ws = arcpy.env.workspace
>>> fc = arcpy.CreateFeatureclass_management(ws, fc_name, "POINT")
>>> arcpy.AddField_management(fc, "ID", "TEXT", field_length=1)
<Result 'C:\\Users\\bixb0012\\Documents\\ArcGIS\\Default.gdb\\tmpFC'>
>>> arcpy.AddField_management(fc, "Version", "LONG")
<Result 'C:\\Users\\bixb0012\\Documents\\ArcGIS\\Default.gdb\\tmpFC'>
>>> cur = arcpy.da.InsertCursor(fc,["ID", "Version"])
>>> [cur.insertRow([i,j]) for i in ("A", "B") for j in (1, 2)]
[1L, 2L, 3L, 4L]
>>> del cur
>>> lyr = arcpy.MakeFeatureLayer_management(fc, lyr_name, "Version = (SELECT MAX(Version) FROM {})".format(fc_name))
>>> arcpy.GetCount_management(lyr)
<Result '2'>
>>> [row for row in arcpy.da.SearchCursor(lyr, ["ID", "Version"])]
[(u'A', 2), (u'B', 2)]
>>>

If the maximum Version number can be different across all IDs at any given time, then the situation gets more complicated because you will have to use a semi-join instead of a basic equality operator.

I recommend looking at query layers regardless of whether the Version numbers increment uniformly. The documentation for SQL reference for query expressions used in ArcGIS points out some important limitations to using subqueries, especially with versioned data:

Coverages, shapefiles, and other nongeodatabase file-based data sources do not support subqueries. Subqueries that are performed on versioned ArcSDE feature classes and tables will not return features that are stored in the delta tables. File geodatabases provide the limited support for subqueries explained in this section, while personal and ArcSDE geodatabases provide full support. For information on the full set of subquery capabilities of personal and ArcSDE geodatabases, refer to your DBMS documentation.

Re: Select Max value arcpy

timdunlevie — Tue, 15 Dec 2015 00:58:21 GMT

Thanks mate.

I didn’t want to bore everyone with the full code as this is a small part of the script.

What I was hoping to do was to simply add an “expression” to either the SelectLayerByAttribute command, or the MakeFeatureLayer command.

(apologies if the format of this code is not what is required).

inFeatures = "MasterFC"

layerName = "Temp"

inField = "ID"

joinTable = "text”

joinField = "Name"

qry = "[Version] = (SELECT MAX() from MasterFC GROUP BY )"

expression = "text.type = ‘active’”

outFeature = “NewTable”

arcpy.MakeFeatureLayer_management (inFeatures, layerName)

arcpy.SelectLayerByAttribute_management(layerName, "NEW_SELECTION", qry)

arcpy.AddJoin_management(layerName, inField, joinTable, joinField)

arcpy.SelectLayerByAttribute_management(layerName, "NEW_SELECTION", expression)

arcpy.CopyFeatures_management(layerName, outFeature)

In the above example I get “ERROR 000358: Invalid expression”

If I modify this to this placing the “qry” in the MakeFeatureLayer command:

arcpy.MakeFeatureLayer_management (inFeatures, layerName, qry)

arcpy.SelectLayerByAttribute_management(layerName, "NEW_SELECTION", qry)

arcpy.AddJoin_management(layerName, inField, joinTable, joinField)

arcpy.SelectLayerByAttribute_management(layerName, "NEW_SELECTION", expression)

arcpy.CopyFeatures_management(layerName, outFeature)

I get an error again with the query….

ERROR 000230: Failed selecting with = (SELECT MAX()

I guess my question is whether it can done this way using an expression parameter in either command? I’m thinking my expression is completely wrong…

Or should I be using a Search cursor with an expression?

And yes – there can potentially be different “versions” for each “ID”…..eg ID A, Version 1, ID C, Version 3, ID F, Version 2 etc…

The Feature Class is not versioned.

Many thanks,

Tim

Re: Select Max value arcpy

JamesCrandall — Tue, 15 Dec 2015 13:30:34 GMT

I don't believe SelectLayerByAttribute_management supports GROUP BY.

Re: Select Max value arcpy

WesMiller — Tue, 15 Dec 2015 15:42:25 GMT

Try using your sql statement in arcmap to make sure it works. I got this to work for me OBJECTID in (select max( OBJECTID ) from bpbSWBilling.DBO.Impervious GROUP BY FEATURE) ,notice I'm using "in" and not "="

Re: Select Max value arcpy

JoshuaBixby — Sat, 11 Dec 2021 22:10:09 GMT

On the surface, it seems like using OBJECTID this way should work, but I also don't understand Esri's sequencing of that field well enough to say accurate results are guaranteed. One of the classic approaches, i.e., has been around for a long time and is SQL ANSI compatible, to solving this problem in the DBMS realm uses NOT EXISTS:

qry = "NOT EXISTS (SELECT 1 FROM fc f WHERE fc.id = f.id AND f.version > fc.version)"
arcpy.SelectLayerByAttribute_management(layerName, "NEW_SELECTION", qry)

One note of caution with using this approach. Although the SQL reference for query expressions used in ArcGIS documentation states, "EXISTS is supported in file, personal, and ArcSDE geodatabases ...," the type of expression above provides bogus results for file geodatabases. The results are accurate for personal geodatabases and all enterprise geodatabases I have tried.

Re: Select Max value arcpy

timdunlevie — Wed, 16 Dec 2015 03:49:25 GMT

Cheers mate.

This gives me no syntax errors when incorporating the qry in the SelectLayerByAttribute command, and seems like it “should’ work.

However, I am still getting both ID A, Version 1 & ID A, Version 2 in the resulting outFC.

I’ll keep playing around with this – I believe it’s what I am looking for!

Thanks,

Tim

Re: Select Max value arcpy

timdunlevie — Wed, 16 Dec 2015 04:59:25 GMT

mate just tested this again and it works! Brilliant!!!!

I had an incorrect reference to my fc in the syntax when I tested it initially!!

Many thanks.

Re: Select Max value arcpy

AndresCastillo — Tue, 05 Jun 2018 13:11:23 GMT

I was able to successfully run the sql query you provided on arcmap 10.4 by adding single quotes around the column names, and replacing the letters fc with the actual table name.

See Chris Wells' comments in this thread:

https://community.esri.com/thread/24148

This is my definition query within arcmap:

NOT EXISTS (SELECT 1 FROM Sanitary.sde.ssManholeInspectionsHistory_evw f WHERE 'Sanitary.sde.ssManholeInspectionsHistory_evw.ParentGUID' = 'f.ParentGUID' AND 'f.InsStart' > 'Sanitary.sde.ssManholeInspectionsHistory_evw.InsStart')‍‍

There are no error messages when I validate the query.

Yet, when I execute the query, I am getting the same number of records I had when there was no query.

FYI,

The ParentGUID field is a GUID Data type, and contains duplicate IDs.

The InsStart field is a Datetime Data type, and contains unique Dates for every record.

Joshua Bixby,

Can you please explain what this NOT EXISTS sql statement is doing, and how I may fix what I may be doing wrong?

Re: Select Max value arcpy

JoshuaBixby — Tue, 05 Jun 2018 14:56:28 GMT

What is your EGDB, SQL Server or Oracle or something else? What version? What version is the EGDB itself?

What you have done by adding the single quotes around your field names is to have your WHERE clause in the nested SELECT statement compare string literals, not fields in the database. For example,

'Sanitary.sde.ssManholeInspectionsHistory_evw.ParentGUID' = 'f.ParentGUID'‍

will always return False since the 2 strings are not the same. In short, your query isn't doing what you think or would like it to do.

When it comes to passing anything but the most trivial WHERE clause through ArcGIS, it is always helpful to access the DBMS directly using something like SQL Server Management Studio, if the EGDB is SQL Server, to troubleshoot the query.

If you remove the single quotes, what happens?

Re: Select Max value arcpy

AndresCastillo — Tue, 05 Jun 2018 17:07:55 GMT

SQL server 2014.120.5579.0

EGDB is 10.4.1

Removing the single quotes within arcmap definition query gave me the following message:

Re: Select Max value arcpy

JoshuaBixby — Tue, 05 Jun 2018 17:41:59 GMT

what if you drop the "Sanitary" part from the multi-part identifier?

Re: Select Max value arcpy

AndresCastillo — Tue, 05 Jun 2018 17:52:06 GMT

I tried:

NOT EXISTS (SELECT 1 FROM [sde].[ssManholeInspectionsHistory_evw] f WHERE [sde].[ssManholeInspectionsHistory_evw].[ParentGUID] = f.ParentGUID AND f.InsStart > [sde].[ssManholeInspectionsHistory_evw].[InsStart])‍

NOT EXISTS (SELECT 1 FROM [sde].[ssManholeInspectionsHistory_evw] f WHERE [sde].[ssManholeInspectionsHistory_evw].ParentGUID = f.ParentGUID AND f.InsStart > [sde].[ssManholeInspectionsHistory_evw].InsStart)‍

NOT EXISTS (SELECT 1 FROM sde.ssManholeInspectionsHistory_evw f WHERE sde.ssManholeInspectionsHistory_evw.ParentGUID = f.ParentGUID AND f.InsStart > sde.ssManholeInspectionsHistory_evw.InsStart)‍

None worked.

Re: Select Max value arcpy

JoshuaBixby — Tue, 05 Jun 2018 17:59:39 GMT

The screenshot error holds the clue, i.e., a multi-part identifier could not be bound. It might be worth searching on that error and seeing what you can find. As I mentioned earlier, troubleshooting SQL is much easier with SQL Server Management Studio than through ArcGIS.

Re: Select Max value arcpy

AndresCastillo — Tue, 05 Jun 2018 18:20:16 GMT

Yes, I searched, and it brought me to the thread that suggested the single quote.

My DBA and I tried looking at it on ssms, but couldn't figure it out.

I will continue searching for more solutions.

Thank you Joshua.

Re: Select Max value arcpy

JoshuaBixby — Tue, 05 Jun 2018 19:30:48 GMT

My original example involved tables, not views. From a quick search online, this type of SQL NOT EXISTS gets tricky when joins are involved, which there are multiple joins in an Esri versioned view of a feature class.

Re: Select Max value arcpy

AndresCastillo — Wed, 06 Jun 2018 13:23:54 GMT

I see what you mean.

When I removed the '_evw' from the query, it worked.

Unfortunately, I'd rather see the edits in real time.

I might end up going with a spatial database view, which will allow me to see these real time.

The spatial database view would be something along the lines of the following when trying to symbolize a feature class from the attribute fields of a table:

CREATE VIEW TestAndresMaxDateEquals AS SELECT ss.OBJECTID AS 'FC_LONGINTEGEROID', ss.GlobalID AS 'FC_Global_ID', ss.SHAPE AS 'ESRI_Shape', ssh.InsStatus AS 'Inspection_Status', ssh.REPSTATUS AS 'Repair_Status', ssh.CONDITION_GENERAL AS 'General_Condition', ssh.InsStart AS 'Inspection_Date_Time', ssh.ParentGUID AS 'Parent_GUID' FROM Sanitary.sde.ssManholeInspections_evw AS ss JOIN Sanitary.sde.ssManholeInspectionsHistory_evw AS ssh ON ss.GlobalID = ssh.ParentGUID WHERE ssh.InsStart = (SELECT MAX(ssh2.InsStart) FROM Sanitary.sde.ssManholeInspectionsHistory_evw AS ssh2 WHERE ssh2.ParentGUID = ssh.ParentGUID)‍‍‍‍‍

Caveats:

I will have to register the FC and Table involved in this view as versioned with the option to move edits to base, which may not detect conflicts on the edits.

I am willing to overlook this because I think conflict would be minimal in my application.

So that my application can see the edits real-time, I will publish the source mxd with the layers pointing to the default version.

I will update this if I decide something else here, and in my post:

https://community.esri.com/thread/215046-symbolizing-feature-class-using-related-table-values

Please let me know if you see any weaknesses in my query.

I can't thank you enough Joshua for your help.

EDIT:

I am debating whether or not to replace the '=' operator with the 'in' operator for the subquery in my query statement.

Any feedback?