Field calculator with Python or SQL

[MDBaky]

Hi mates,

I would like to calculate/fill the fields- 'River' with the values either 'Yes' or 'No' depending on the maximum value in the field named 'AdjRiverLe' and the field named 'ID'. See the image here.

The rule will be:

Yes= The maximum value in the field 'AdjRiverLe' group by the field 'ID'

No= the rest of the values in the field 'AdjRiverLe'

For example, the ID 1 has three individual rows with three different values in the field- 'AdjRiverLe '.  I would like to select the row which belongs to the maximum values in the field - 'AdjRiverLe' and fill the field 'River' with 'Yes'.

I reckon the code block option would work.

[Waffle_House]

Yes, the code block would work for your situation.

[MDBaky]

Thanks Waffle for you reply. But would you please help me to figure the code block writing out?

[JoshuaBixby]

Questions along these lines get asked fairly regularly on the Esri Community, probably every 4-6 months.  Although it is possible to answer your question using Field Calculator, it is quite a clunky approach in my mind.  This kind of question is best suited for ArcPy cursors:

lyr = # name of layer or path to data set
flds = ["ID", "AdjRiverLe", "River"]
sql = "ORDER BY ID, AdjRiverLe DESC"

with arcpy.da.UpdateCursor(lyr, flds, sql_clause=(None, sql)) as cur:
    rid, adj_river, river = next(cur)
    river = "YES"
    cur.updateRow([rid, adj_river, river])
    prev_rid = rid
    
    for rid, adj_river, _ in cur:
        if rid == prev_rid:
            river = "NO"
        else:
            river = "YES"
        cur.updateRow([rid, adj_river, river])
        prev_rid = rid

[jcarlson]

Since you mention SQL, is the layer in question in an Enterprise GeoDatabase, or is it a feature service? There are a couple of ways you might approach this, but it may depend on where the features are coming from.

[jcarlson]

Also, though you didn't mention Arcade, It's worth pointing out that this can be done simply enough in Arcade.

// Get features w/ matching ID
var id = $feature.ID
var id_feats = Filter(FeatureSetByName($datastore, 'polygon-layer-name'), 'ID = @ID')

// Get maximum value of AdjRiverLe field
var max_le = Max(id_feats, 'AdjRiverLe')

// Assign 'Yes' to max value, 'No' to others
if($feature.AdjRiverLe == max_le){
    return 'Yes'
} else {
    return 'No'
}