nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Select Max value arcpy

TimDunlevie2

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

Find more posts tagged with

Accepted answers

JoshuaBixby

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.

All comments

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,

<SPAN class="string token">'Sanitary.sde.ssManholeInspectionsHistory_evw.ParentGUID'</SPAN> <SPAN class="operator token">=</SPAN> <SPAN class="string token">'f.ParentGUID'</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

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

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:

<SPAN class="operator token">NOT</SPAN> <SPAN class="keyword token">EXISTS</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="keyword token">SELECT</SPAN> <SPAN class="number token">1</SPAN> <SPAN class="keyword token">FROM</SPAN> Sanitary<SPAN class="punctuation token">.</SPAN>sde<SPAN class="punctuation token">.</SPAN>ssManholeInspectionsHistory_evw <SPAN class="number token">f</SPAN> <SPAN class="keyword token">WHERE</SPAN> <SPAN class="string token">'Sanitary.sde.ssManholeInspectionsHistory_evw.ParentGUID'</SPAN> <SPAN class="operator token">=</SPAN> <SPAN class="string token">'f.ParentGUID'</SPAN> <SPAN class="operator token">AND</SPAN> <SPAN class="string token">'f.InsStart'</SPAN> <SPAN class="operator token">></SPAN> <SPAN class="string token">'Sanitary.sde.ssManholeInspectionsHistory_evw.InsStart'</SPAN><SPAN class="punctuation token">)</SPAN>‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

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?

TimDunlevie2

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.

TimDunlevie2

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

JoshuaBixby

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)

WesMiller

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 "="

JamesCrandall

I don't believe SelectLayerByAttribute_management supports GROUP BY.

TimDunlevie2

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

JoshuaBixby

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.

ChrisSmith7

Advanced editor works here!

DanPatterson_Retired

error message?

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.