topic Re: To select a record based on the highest value of a field? in Python Questions

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

ionarawilson1 — Tue, 09 Jul 2013 13:17:26 GMT

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

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

JamesCrandall — Tue, 09 Jul 2013 13:39:19 GMT

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

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

RhettZufelt — Sat, 11 Dec 2021 15:59:49 GMT

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_

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

JamesCrandall — Tue, 09 Jul 2013 16:32:09 GMT

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.

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

RhettZufelt — Tue, 09 Jul 2013 16:36:30 GMT

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_

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

ionarawilson1 — Sat, 11 Dec 2021 15:59:51 GMT

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)

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

ionarawilson1 — Sat, 11 Dec 2021 15:59:54 GMT

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)

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

ionarawilson1 — Sat, 11 Dec 2021 15:59:57 GMT

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)

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

RhettZufelt — Sat, 11 Dec 2021 15:59:59 GMT

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_

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

RhettZufelt — Sat, 11 Dec 2021 16:00:02 GMT


    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.

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

RhettZufelt — Sat, 11 Dec 2021 16:00:05 GMT

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.

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

ionarawilson1 — Sat, 11 Dec 2021 16:00:08 GMT

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)

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

RhettZufelt — Sat, 11 Dec 2021 16:00:10 GMT

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_

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

RhettZufelt — Sat, 11 Dec 2021 16:00:13 GMT

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_

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

ionarawilson1 — Tue, 09 Jul 2013 20:32:03 GMT

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

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

RhettZufelt — Sat, 11 Dec 2021 16:00:16 GMT

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

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

ionarawilson1 — Tue, 09 Jul 2013 20:35:44 GMT

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

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

ionarawilson1 — Tue, 09 Jul 2013 20:42:33 GMT

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!

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

RhettZufelt — Sat, 11 Dec 2021 16:00:18 GMT

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_

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

ionarawilson1 — Tue, 09 Jul 2013 20:43:42 GMT

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