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; } } }

ArcGIS 3.0.2: What tool can select the records with the highest values in a particular field?

JamalNUMAN

For example, in the screenshot below, I couldn’t figure out which tool can select the FOUR records with the highest population size in the “population” field

Najděte více příspěvků označených jako

Accepted answers

Hornbydd

I have edited the model to work as intended, please review comments in model.

H_edited.zip

Bud

I wonder if this would work in your model:

Use the Sort geoprocessing tool to output a FC that is sorted by the population field (desc).
Use Select By Attributes or a definition query to isolate the first 4 ObjectIDs.

Maybe that's the kind of thing @MErikReedAugusta had in mind in my ROW_NUMBER idea.

All comments

Eugene_Adkins

Determine the threshold and use the select by attribute tool.

Hornbydd

As long as your data is in a file geodatabase (so not a shape file) you can use a subquery to answer this.

An example is shown below, in this example ORN is the layer.

gargarcia

If you use the data engineering pane and calculate the stats for an integer field it will list the min and max values and let you select them with a right click.

JamalNUMAN

What sql code can select the FOUR records of the highest population size from the “population” field?

Hornbydd

My apologies I missed that you wanted the top four values. I'm not sure (but I could be wrong) that a file geodatabase can support the functions that would allow you to answer this question and you need to approach it in a two-step approach in modelbuilder, this how I would have done it.

I show this below, in my example I am processing a layer called ORN:

The Calculate Value tool is set up as-is, note it returns double:

The code block is:

def Fourth(layer):
    # You need to set field to the appropriate field
    field = "SourceID"
    s = set()
    with arcpy.da.SearchCursor(layer, field) as cursor:
        for row in cursor:
            if row[0] is not None:
                s.add(row[0])
    l = sorted(s, reverse=True)
    ml = l[0:4]  # first 4 values in sorted list
    mv = min(ml) # Get min value
    return mv

The select by attribute tool is:

This logic will select the top four values in the field sourceID, be aware that the selection may return a selection greater than 4 if you have duplicate values in your field. Also underlying assumption of the code is that you have a minimum of 4 unique values in your field.

JamalNUMAN

Many thanks for the help

I couldn’t customize the code you have already provide to my local data. In addition to that, the content of the “calculate value” tool in my Pro appears to be different than yours as per the screenshot below.

I’m attaching my data along with the model I tried to build with no success

H.gdb.zip

Hornbydd

I have edited the model to work as intended, please review comments in model.

H_edited.zip

Bud

I submitted an idea: Use Select by Attributes to select greatest n records (FGDB)

Bud

I wonder if this would work in your model:

Use the Sort geoprocessing tool to output a FC that is sorted by the population field (desc).
Use Select By Attributes or a definition query to isolate the first 4 ObjectIDs.

Maybe that's the kind of thing @MErikReedAugusta had in mind in my ROW_NUMBER idea.

Bud

Can you elaborate on how "Determine the threshold" would be done in a model?

Hornbydd

I had considered that approach too as it works just as well, the only "issue" would be that sort creates an output table and the user would need to deal with that (i.e. keep it or delete it). If I had taken this route I would probably have written it to an in-memory workspace.

Anyway nice to have shown two solutions for the question!

JamalNUMAN

Many thanks guys for the help. It works fine

Bud

Top-n in primitive database (file geodatabase)
It's kind of brute force elimination and probably won't work right if there are rows with exact same value in population field.

population = (select max(population) from cities)
or population = (select max(population) from cities where population < (select max(population) from cities))
or population = (select max(population) from cities where population < (select max(population) from cities where population < (select max(population) from cities)))
or population = (select max(population) from cities where population < (select max(population) from cities where population < (select max(population) from cities where population < (select max(population) from cities))))

Bud

Idea: Make Query Table — Sort data (and then generate dynamic OBJECTID)

JamalNUMAN

I can live with the "sort" and "select" tools to find the records with the top values. However, this may not work correctly if two of the top records have the same value.

Připojte se ke konverzaci

Přihlášení členové mohou přispívat, sledovat aktualizace a další. Jste tu noví? Zaregistrujte si bezplatný účet.

Rychlé odkazy

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