Select Max value arcpy

[timdunlevie]

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

[AndresCastillo]

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?

[JoshuaBixby]

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?

[AndresCastillo]

SQL server 2014.120.5579.0

EGDB is 10.4.1

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

[JoshuaBixby]

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

[AndresCastillo]

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.

[JoshuaBixby]

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.

[AndresCastillo]

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.

[JoshuaBixby]

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.

[AndresCastillo]

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:

1.

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.

2.

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?