Calculate Field using sequential numbers, partition by GROUP_ID field, order by VALUES field

[Bud]

ArcGIS Pro 3.3.1; file geodatabase

I want to use the field calculator to populate a new/blank field called ROW_NUM.

• For each group, using sequential numbers starting at 1, ordered by the VALUES field (descending)…populate the ROW_NUM field.

GROUP_ID VALUES ROW_NUM
1        0      2
1        1      1
1        0      3

2        0      1
2        0      2

3        1      1
3        0      3
3        1      2

It is my understanding that can’t currently be done using the Calculate Field tool (Python). Could that functionality be added?

If it were an Oracle SQL query, it would look like this:

select
    GROUP_ID,
    VALUES,
    row_number() over (partition by GROUP_ID order by VALUES desc) as rn
from
    MY_TABLE

[Bud]

ROW_NUMBER function in FGDB SQL

[JonathanNeal]

@Bud What do you think about using this altered Sequential number?
Calculate Field (Data Management)—ArcGIS Pro | Documentation

# Calculates a sequential number
# More calculator examples at esriurl.com/CalculatorExamples
rec={}
def SequentialNumberByGroup(group):
global rec
pStart = 1
pInterval = 1
if group in rec:
rec[group] = rec[group] + pInterval
else:
rec[group] = pStart
return rec[group]

[Bud]

@JonathanNeal Thanks. At first glance, that script doesn't seem to order the sequential numbers by the VALUES field (descending), which is a requirement.

[JonathanNeal]

@Bud If sorting is a requirement, then the Sort tool will need to be ran first.  Interesting idea though, to force processing order with the Calculate Field tool.