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

ionarawilson1 · ‎07-09-2013

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

RhettZufelt · ‎07-09-2013

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.

ionarawilson1 · ‎07-09-2013

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 · ‎07-09-2013

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 · ‎07-09-2013

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_

ionarawilson1 · ‎07-09-2013

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

RhettZufelt · ‎07-09-2013

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

R_

ionarawilson1 · ‎07-09-2013

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

ionarawilson1 · ‎07-09-2013

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 · ‎07-09-2013

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_

ionarawilson1 · ‎07-09-2013

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