Selecting maximum Value based on other field using ArcPy

[badrinathkar]

I want to populate a field (MaxElv) selecting the highest value from another field (Elevation) based on the Id field (Id). The data looks like this:

Id   Elevation   MaxElv
1     50      
1     72
1     83
1     66
2    105
3     27
3     24
2     98
1     46
3     22
2     96
3     26
2     99
4     25
4     21
4     22
3     28
1     52

I have develop the following code:
>>> import arcpy
... fc = 'Country'
... CountryList = [] 
... with arcpy.da.SearchCursor(fc, ["Id"]) as cur1:
... for row1 in cur1:
... if row1[0] not in CountryList:
... CountryList.append(row1[0])
... 
... for i in CountryList:
... ElevList = []
... where = """{} = {}""".format("Id", i)
... with arcpy.da.SearchCursor(fc, ["Elevation"], where) as cur2:
... for row2 in cur2:
... if row2[0] not in ElevList:
... ElevList.append(row2[0])
... ElevList.sort()
... max_Elev= ElevList[-1]
... where2 = """{} = '{}' AND {} = {}""".format("Id", i, "Elevation", max_Elev)
... with arcpy.da.UpdateCursor(fc, ["MaxElv"], where2) as cur3:
... for row3 in cur3:
... row3[0] = 1
... cur3.updateRow(row3)


But it gives the following error
Runtime error

>>> import arcpy
... fc = 'Country'
... CountryList = [] 
... with arcpy.da.SearchCursor(fc, ["Id"]) as cur1:
... for row1 in cur1:
... if row1[0] not in CountryList:
... CountryList.append(row1[0])
... 
... for i in CountryList:
... ElevList = []
... where = """{} = {}""".format("Id", i)
... with arcpy.da.SearchCursor(fc, ["Elevation"], where) as cur2:
... for row2 in cur2:
... if row2[0] not in ElevList:
... ElevList.append(row2[0])
... ElevList.sort()
... max_Elev= ElevList[-1]
... where2 = """{} = '{}' AND {} = {}""".format("Id", i, "Elevation", max_Elev)
... with arcpy.da.UpdateCursor(fc, ["MaxElv"], where2) as cur3:
... for row3 in cur3:
... row3[0] = 1
... cur3.updateRow(row3)


But it gives the following error
Runtime error 
Traceback (most recent call last):
File "<string>", line 20, in <module>
RuntimeError: An invalid SQL statement was used.

Traceback (most recent call last):
File "<string>", line 20, in <module>
RuntimeError: An invalid SQL statement was used.

[JayantaPoddar]

It would help if you could add the Python script using "Insert/Edit Code Sample" option.

[badrinathkar]

Sir, I have edited the post. Please see.

[JoshuaBixby]

Questions along these lines get asked fairly regularly on the Esri Community, probably every 4-6 months.  There is no singular way to solve the question, but some ways are much more efficient than others.  In your case, the approach is one of the less efficient ones, so I won't discuss your invalid SQL statement but suggest a different approach using SQL sorting.

lyr = # name of layer or path to data set
flds = ["Id", "Elevation", "MaxElv"]
sql = "ORDER BY Id, Elevation DESC"

with arcpy.da.UpdateCursor(lyr, flds, sql_clause=(None, sql)) as cur:
    eid, elev, max_elev = next(cur)
    max_elev = elev
    cur.updateRow([eid, elev, max_elev])
    prev_eid = eid
    
    for eid, elev, _ in cur:
        if eid != prev_eid:
            max_elev = elev
        cur.updateRow([eid, elev, max_elev])
        prev_eid = eid

[badrinathkar]

Sorry to say! But your script populates the MaxElv field only with the value of Elevation with which it strikes first for the same Ids.

[JayantaPoddar]

sql = "ORDER BY Id, Elevation DESC"

I feel this line does the trick. The first Elevation Value for each ID is the Max_Elev value.

Joshua's script works perfectly for me.

What results/anomalies are you getting (screenshot)? 

[badrinathkar]

Sir, please see the attached figure.

[JoshuaBixby]

I see you have an "FID" field, I am assuming you are working with shape files.  If you consult the documentation, UpdateCursor—ArcMap | Documentation (arcgis.com)

DISTINCT, ORDER BY, and ALL are only supported when working with databases. They are not supported by other data sources (such as dBASE or INFO tables).

In short, my approach won't work for you because you are working with shape files.

[badrinathkar]

I want to populate a field (MaxElv) selecting the highest value from another field (Elevation) based on the Id field (Id). The data looks like this:

Id   Elevation   MaxElv
1     50      
1     72
1     83
1     66
2    105
3     27
3     24
2     98
1     46
3     22
2     96
3     26
2     99
4     25
4     21
4     22
3     28
1     52

I have develop the following code:
>>> import arcpy
... fc = 'Country'
... CountryList = [] 
... with arcpy.da.SearchCursor(fc, ["Id"]) as cur1:
... for row1 in cur1:
... if row1[0] not in CountryList:
... CountryList.append(row1[0])
... 
... for i in CountryList:
... ElevList = []
... where = """{} = {}""".format("Id", i)
... with arcpy.da.SearchCursor(fc, ["Elevation"], where) as cur2:
... for row2 in cur2:
... if row2[0] not in ElevList:
... ElevList.append(row2[0])
... ElevList.sort()
... max_Elev= ElevList[-1]
... where2 = """{} = '{}' AND {} = {}""".format("Id", i, "Elevation", max_Elev)
... with arcpy.da.UpdateCursor(fc, ["MaxElv"], where2) as cur3:
... for row3 in cur3:
... row3[0] = 1
... cur3.updateRow(row3)


But it gives the following error
Runtime error 
Traceback (most recent call last):
File "<string>", line 20, in <module>
RuntimeError: An invalid SQL statement was used.

[JoshuaBixby]

Question cross posted:  https://community.esri.com/t5/arcmap-questions/selecting-maximum-value-based-on-other-field-using-ar....  @badrinathkar , I suggest deleting this one since people have started responding to the other one.  In the future, Python-related questions that are not specific to ArcMap or Pro can be asked https://community.esri.com/t5/python/ct-p/python