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

To select a record based on the highest value of a field?

WilsonIonara

Is there a way to select a record based on the highest value of a field? And also is there a way to check if a record is selected based on the highest value before I update a record? Thanks

Trouver plus de publications taguées avec

Accepted answers

JamesCrandall

You could use a cursor with a sort on it:

infc = my input feature class
myField = field I am after the max value from
OID = ObjectID field of my FC.
maxValue = arcpy.SearchCursor(infc, "", "", "", myField+ " D").next().getValue(OID)  
change the myField field to the one you want the max value from and grab the "OID" of that record as maxValue variable.

this would give you the values, could then use that to select by.

R_

NICE!!! I will def use this one, Rhett.

All comments

JamesCrandall

Is there a way to select a record based on the highest value of a field? And also is there a way to check if a record is selected based on the highest value before I update a record? Thanks

I don't have a specific example to show, but I can think of a few ways to go:

1. Summary Statistics usage. This would require you to output a table to some location, then you could grab the max value for the field you are summarizing on. Probably not best for an in-memory workflow though. http://resources.arcgis.com/en/help/main/10.1/index.html#//00080000001z000000

2. You could convert your fc/table to a numpy array and then derive the the max value from the array. This may be your most direct approach and can be done without writing anything to disk. Look at the code sample in the link as it shows a very simplified way to get your statistic (you could just set a variable to your max value then use it later in your updateCursor).

http://resources.arcgis.com/en/help/main/10.1/index.html#//018w00000015000000

3. I suppose you could also just create a basic list of values from your field you want to summarize the max value for and set this to a variable.

I'd probably opt for the #2nd option myself.
j

RhettZufelt

You could use a cursor with a sort on it:

infc = my input feature class
myField = field I am after the max value from
OID = ObjectID field of my FC.

maxValue = arcpy.SearchCursor(infc, "", "", "", myField+ " D").next().getValue(OID)

change the myField field to the one you want the max value from and grab the "OID" of that record as maxValue variable.

this would give you the values, could then use that to select by.

R_

JamesCrandall

You could use a cursor with a sort on it:

infc = my input feature class
myField = field I am after the max value from
OID = ObjectID field of my FC.
maxValue = arcpy.SearchCursor(infc, "", "", "", myField+ " D").next().getValue(OID)  
change the myField field to the one you want the max value from and grab the "OID" of that record as maxValue variable.

this would give you the values, could then use that to select by.

R_

NICE!!! I will def use this one, Rhett.

RhettZufelt

Basically, it is just sorting the table decending by myField, then grabbing the "first" row.

Change the " D" to " A" and will sort ascending and would grab the minimum value instead.

R_

WilsonIonara

Hi Rhett, so if I want to update something after the search cursor, what do I do. I tried to update a field but of course it did not work, so in this example, what should I change? Thank you so much !!!


    maxValue = arcpy.SearchCursor("Stewardship", "", "", "", "OBJECTID  A").next().getValue("OBJECTID")
    with maxValue as rows:
        # row comes back as a tuple in the order specified here, so Office is row[0], Forester is row[1]
        yearonly = DateStart[5:9]

        for row in rows:
            row[0] = yearonly


            rows.updateRow(row)

WilsonIonara

Rhett, how can I run any process after I create the search cursor, because right now the cursor is pointing to an integer object (OBJECTID), but how can I select this value with the highest OBJECTID and use in a process? Right now what I have below is not going to work because it is going to look at the records and not only the one with the highest OBJECTID

Thank you!!!!

  maxValue = arcpy.SearchCursor("Stewardship", "", "", "", "OBJECTID  A")
    for srow in maxValue:
     result = int(arcpy.GetCount_management("Stewardship").getOutput(0))
     arcpy.AddMessage(result)

WilsonIonara

I think I am getting there but still getting an error for the first line. Do you see anything wrong on the first line? Thanks!!!!
I am getting this error:

Error Info:
<type 'exceptions.RuntimeError'>: ERROR 999999: Error executing function.

 maxValue = arcpy.SearchCursor("Stewardship", "", "", "", "OBJECTID  A").next().getValue("OID")
    arcpy.SelectLayerByAttribute_management("Counties",  "NEW_SELECTION", maxValue)
    result = int(arcpy.GetCount_management("Stewardship").getOutput(0))
    arcpy.AddMessage(result)

RhettZufelt

I think I am getting there but still getting an error for the first line. Do you see anything wrong on the first line? Thanks!!!!
I am getting this error:

Error Info:
<type 'exceptions.RuntimeError'>: ERROR 999999: Error executing function.
 maxValue = arcpy.SearchCursor("Stewardship", "", "", "", "OBJECTID  A").next().getValue("OID")
    arcpy.SelectLayerByAttribute_management("Counties",  "NEW_SELECTION", maxValue)
    result = int(arcpy.GetCount_management("Stewardship").getOutput(0))
    arcpy.AddMessage(result)

Got a fire going in a few minutes, so I'll have to try to get back to previous posts later, but it seems you are on the right track. However, will need to make a feature layer before selection as selectbyatt can't utilize a FC directly.

As far as the error, probably your OID field name, it can't be both. you are sorting on OBJECTID, but trying to extract a value from OID.

R_

R_

RhettZufelt


    rows = arcpy.UpdateCursor("Stewardship", "", "", "", "OBJECTID  A")
       row = rows.next()
       row.yearonly = DateStart[5:9]
       rows.updateRow(row)

another quick thought before I run, UpdateCursor supports the sort also, might try something like above and see if it works correctly.

This is how I define row(s) values without having to actually iterate through the cursor. Of course, you need to have a field in Stewardship FC named "yearonly".

Should update the field "yearonly" to the value in stored in the variable DateStart[5:9].

R_

Of course, I noticed that the " D" was changed to " A", so this should actually grab the minimum value, not the max.

RhettZufelt

Fire got put out before I made it. :DIf you don't just need to update, but actually need a "selection" to use in a process, this is the line of thought I was thinking:


minValue = arcpy.SearchCursor("Stewardship", "", "", "", "OID  A").next().getValue("OID")

arcpy.MakeFeatureLayer_management ("Stewardship", "feat_layer")


arcpy.SelectLayerByAttribute_management ("feat_layer", "NEW_SELECTION", " [OID] = " + maxValue)

Whatever you do now on "feat"layer" will honor the selection if that tool supports it.

R_

This one I changed to minValue since we are sorting Ascending so would get the min as the first row.

WilsonIonara

I have OBJECTID, not OID, however when I try your code it prints always the first record. I tried both with A and D. I tried sorting by SequenceNumber (numbers from 1 to 8), and I tried sorting by OBJECTID. Which I get is always the first number. So for example, SequenceNumber 1 I get OBJECTID 2451 (object id for sequence number 1) and if I sort by OBJECTID, I get 2445, no matter what (the objectids go from 2445 to 2451). I am using SQL feature class. Any idea why this is happening? Also, you mentioned you can't do anything with a fc for a selection. Is that the same with a selection by location? Because I was able to select by location just using the fc name ("Stewardship), without creating a feature layer. Would you know why that worked? Thank you!!!

    maxValue = arcpy.SearchCursor("Stewardship", "", "", "", "OBJECTID  D").next().getValue("OBJECTID")
    arcpy.AddMessage(maxValue)

RhettZufelt

You could use a cursor with a sort on it:

infc = my input feature class
myField = field I am after the max value from
OID = ObjectID field of my FC.



[maxValue = arcpy.SearchCursor(infc, "", "", "", myField+ " D").next().getValue(OID)

change the myField field to the one you want the max value from and grab the "OID" of that record as maxValue variable.

this would give you the values, could then use that to select by.

R_

Ionara,

In my original posting of this code, I have the myField+" D" section that you have changed to the OBJECTID field. This is the field that it is sorting on, so using the OID field, will get you the first (+ " A") or last (+ " D") record in the table.
If you are trying to find a max or min value of a particular field, you need to have that field set as the sort field. " D" sorts the table by field and would put the largest value as the first row. The .next() grabs the next (in this case the first) row, that being sorted descending, is the max.

R_

RhettZufelt

I have OBJECTID, not OID, however when I try your code it prints always the first record. I tried both with A and D. I tried sorting by SequenceNumber (numbers from 1 to 8), and I tried sorting by OBJECTID. Which I get is always the first number. So for example, SequenceNumber 1 I get OBJECTID 2451 (object id for sequence number 1) and if I sort by OBJECTID, I get 2445, no matter what (the objectids go from 2445 to 2451). I am using SQL feature class. Any idea why this is happening? Also, you mentioned you can't do anything with a fc for a selection. Is that the same with a selection by location? Because I was able to select by location just using the fc name ("Stewardship), without creating a feature layer. Would you know why that worked? Thank you!!!
    maxValue = arcpy.SearchCursor("Stewardship", "", "", "", "OBJECTID  D").next().getValue("OBJECTID")
    arcpy.AddMessage(maxValue)

Yes, the select by location needs a feature layer also. For you to be able to create a selection directly on the FC, I would assume that you are running from within ArcMap. If this is the case, ArcMap "secretly" and "silently" converts FC's in the TOC to a feature layer that can be used for input to the tools. if a feature layer is needed, and you choose the dropdown list in the tool to select the FC, the visible FC's are actually an on the fly feature layer. So, I guess if this is always run from within ArcMap, you woulnd't need that step as it is done for you.

Of course, if you export that script/model to python, it doesn't do this part for you and you need to add in the make feature or the script will error out.

R_

WilsonIonara

I am actually running the script I am writing so I am not directly using the tool. Not sure why it works.

RhettZufelt

I have OBJECTID, not OID, however when I try your code it prints always the first record. I tried both with A and D. I tried sorting by SequenceNumber (numbers from 1 to 8), and I tried sorting by OBJECTID. Which I get is always the first number. So for example, SequenceNumber 1 I get OBJECTID 2451 (object id for sequence number 1) and if I sort by OBJECTID, I get 2445, no matter what (the objectids go from 2445 to 2451). I am using SQL feature class. Any idea why this is happening? Also, you mentioned you can't do anything with a fc for a selection. Is that the same with a selection by location? Because I was able to select by location just using the fc name ("Stewardship), without creating a feature layer. Would you know why that worked? Thank you!!!
    maxValue = arcpy.SearchCursor("Stewardship", "", "", "", "OBJECTID  D").next().getValue("OBJECTID")
    arcpy.AddMessage(maxValue)

Ionara,

Well, this was a little confusing as I get the same result each time I run the script also, unless I type it directly into the console. If I type it in, I get the max and min numbers. If I run the code, I get the min number each time.

Turns out there is a copy/paste/formatting issues somewhere. the "OBJECTID D" should have a space in between the "OBJECTID" and the "D". However, it appears as if there is a tab character there, so it is getting ignored in the cursor so the table is sorted by default (OID Ascending). Once I put a space in there as expected, it successfully gets both the max and min.

maxValue = arcpy.SearchCursor(infc, "", "", "", "OBJECTID D").next().getValue("OBJECTID")
max Value =  2110
minValue = arcpy.SearchCursor(infc, "", "", "", "OBJECTID A").next().getValue("OBJECTID")
min Value =  1

WilsonIonara

I am still getting the same value. So dumb question here:

I have:

maxValue = arcpy.SearchCursor("Stewardship", "", "", "", "OBJECTID A").next().getValue("OID")

And you have:

[maxValue = arcpy.SearchCursor(infc, "", "", "", myField+ " D").next().getValue(OID)

Am I doing the sorting correctly, because you have the name of field and a plus sign, then the D in parentheses. Thanks

WilsonIonara

Rhett, Please disregard my dumb question 🙂
It was really the problem with the tab! It is working great! I will post another thread if I have any more questions about the updating of rows using this type of selection! Thank you so much! You rock!

RhettZufelt

I am actually running the script I am writing so I am not directly using the tool. Not sure why it works.

Are you running in the python window in ArcMap/Catalog or actually in the IDE or command window?

If I try to run on a FC in the IDE (stand alone without ArcCrap) I get this:

ExecuteError: Failed to execute. Parameters are not valid.
The value cannot be a feature class
ERROR 000840: The value is not a Raster Layer.
ERROR 000840: The value is not a Mosaic Layer.
Failed to execute (SelectLayerByAttribute).

Once I use a feature layer, it works fine.

R_

WilsonIonara

I created a toolbox and added the script and I am running from there, when I click on the script.

RhettZufelt

Rhett, Please disregard my dumb question 🙂
It was really the problem with the tab! It is working great! I will post another thread if I have any more questions about the updating of rows using this type of selection! Thank you so much! You rock!

I was wondering if you posted that before you saw the last post about the tab. That was confusing as could be, as my code "looked" the same, but I got different results typing it in vs running my script.

As far as the sort and fields, the myField+" D" lets me pass the field as a variable so I can use in a def for multiple FC's. As you can see in my last posting, I hard coded it to OBJECTID, so can be done either way.

R_

RhettZufelt

I created a toolbox and added the script and I am running from there, when I click on the script.

Makes sense. So, Arc is doing the featurelayer for you. Keep in mind that if you ever try to export this out as standalone, you will get the error and have to MakeFeatureLayer first for the selection tool.

R_

WilsonIonara

But if somebody has the same feature classes in arcmap and creates a tool and run the script like I am doing, it should work, right?

RhettZufelt

I believe so.

Not sure, but it is my understanding that, running within arc, arc will on the fly create/substitue a feature layer for tools that require it, so if the FC's are the same, I think the tool will work fine.

R_

WilsonIonara

Thank you Rhett, I am going to create another thread because I have a question about the query for the select by attribute parameter! Thank you again!!!

WilsonIonara

James, thank you for the idea about the numpy array. I might try that also!!! Thanks!!!

Rejoindre la conversation

Les membres connectés peuvent publier, suivre les mises à jour, et plus encore. Nouveau ici ? Inscrivez-vous gratuitement.

Liens rapides

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