Selecting maximum values of a field, by group

[YusefSamari]

Hi. I am using ArcGIS Pro 3.1.3. I have a table with columns for species, the month each species is most frequently recorded in, and the number of grid squares each species has been recorded from. I have sorted it ascending by month, then descending by grid squares. I want to select the top 5 species by number of grid squares recorded from, for each month (or less if that month has less than 5 species). For example, I would like to go from:

Species	Month	Squares
A	1	20
B	1	20
C	1	19
D	1	18
E	1	16
F	1	10
G	1	9
H	2	40
I	2	32
J	2	28
K	2	17
L	2	10
M	2	9
N	3	20
O	3	19

To

Species	Month	Squares
A	1	20
B	1	20
C	1	19
D	1	18
E	1	16
H	2	40
I	2	32
J	2	28
K	2	17
L	2	10
N	3	20
O	3	19

Can anyone please help with this? I think the solution should be something similar to what is discussed in this thread - https://community.esri.com/t5/geodatabase-questions/select-maximum-values-in-select-by-attributes/m-... - but I haven't been able to adapt the SQL queries there to my needs. Also not sure what to do in the case of tied values...

Thanks!

[DuncanHornby]

I suspect there are several ways to resolve this and some clever SQL. But this is how I would do it.

• Firstly the table needs to be a file geodatabase table so you can add a new field. So If your data is in Excel then you need to export it.
• Add a new field, call it keep and set it to SHORT.
• The following logic takes advantage of the fact you have pre-sorted the data. Open field Calculate tool and apply the following to keep field.

The code block is:

count = 0
last = 0
def tag(month):
    global count,last
    if last == 0:
        # First row
        last = month
        count = 1
        keep = 1
    elif (month == last) and (count < 5):
        keep = 1
        count += 1
    elif (month == last) and (count >= 5):
        keep = 0
        count += 1
    elif (month != last):
        keep = 1
        count = 1
        last = month
    return keep

• Now run a simple select by attribute to select rows where keep = 1
• Export selection to a new table.

[YusefSamari]

Many thanks for this. I will try it out later and report back. I ran out of time last week because I needed to move forward on this so I just used the 'brute force' option in model builder of selecting each month, sorting by squares field, selecting 'OBJECTID <= 5' then merging them.

[Bud]

@YusefSamari What kind of geodatabase is the data stored in?

Can you attach sample Excel data (or whatever works, such as a zipped file geodarabase or zipped mobile geodatabase) to this post?

[YusefSamari]

The data is in a File Geodatabase. Sample data is exactly like the sample data above - the 'Species' column is a text field, the other two are long integer.

[Bud]

If you're having trouble with the SQL, I would suggest copying the data to a geodatabase type that supports SQL clients, such as a mobile or file geodatabase: https://community.esri.com/t5/geodatabase-questions/select-maximum-values-in-select-by-attributes/m-...

You might ultimately find that file geodatabase SQL is too buggy/problematic.