Selecting maximum Value based on other field using ArcMap?

Question

I have problems with a SQL query. The table looks like this, it is a point feature class both in postgres and file geodatabase, I am working in ArcMap.

name: elev_p
HEIGHT AREAid
100    1
200    1
300    2
500    2
100    1

So, what I want is the highest HEIGHT within each AREA_id. If there are 200 points within each polygon, I only want one, and the one with maximum HEIGHT.

Is this possible in ArcMap or do I have to use SDE layer and pgAdmin?

This AREA_id comes from polygon layer which I joined with elev_p, I tried other way around and I got the maximum HEIGHT but I need to know which exactly point it is, so its selection I need.

WHAT I HAVE TRIED: because one copy (sde version) can handle subquery I used HEIGHT in (SELECT MAX(HEIGHT) FROM elev_p group by AREA_id). But that query selected all Height values in entire feature class which had the highest value in AREA_id, not one for each AREA_ID.

SOLUTION: SELECT * FROM elev_p a INNER JOIN (SELECT MAX(HEIGHT) as bheight, AREAid FROM elev_p GROUP BY AREAid) b ON a.AREAid = b.AREAid AND a.HEIGHT = b.HEIGHT;

note: this is for spatial data in database like oracle or postgres, arcmap solution I do not have

also, if there is 2 points with same value wich is highest within AREAid then it will choose both of them, in case someone needs only exactly one

Answer 1

I would recommend to use python for this, the general code flow would go something like this:

1. Use arcpy.da.search cursor to iterate through AREAid column
2. Nest another arcpy.da.search cursor (with sql expression of AREAid value to filter by) to get HEIGHT values and use a max() method on the cursor to get max value (e.g. highValue = max(cursor))
3. Within second search cursor use arcpy.da.update cursor (supply sql expression to look at specific AREAid and highValue record) and apply attribute to tag record as highest value to some other field/column that you can query against.

Answer 2

Here is a piece of code based on @artwork21's proposed workflow. It uses arcpy.da cursors so you need to have at least a 10.1 license. I've assumed the fields have the following format:

• height: numeric
• area_id: numeric or text (see comments in code)
• highest: numeric (short)

import arcpy
points = r"D:\test.gdb\points"

## make a list of unique area_id values
AreaList = []       
with arcpy.da.SearchCursor(points, ["area_id"]) as cur1:
    for row1 in cur1:
        if row1[0] not in AreaList:
            AreaList.append(row1[0])

## for every area_id, make a list of unique height values and get the highest value
for i in AreaList:
    HeightList = []
    where = """{} = '{}'""".format("area_id", i)    # use this line if the area_id field is text.
#    where = """{} = {}""".format("area_id", i)    # or use this line if the area_id field is numeric.
    with arcpy.da.SearchCursor(points, ["height"], where) as cur2:
        for row2 in cur2:
            if row2[0] not in HeightList:
                HeightList.append(row2[0])
    HeightList.sort()
    max_height = HeightList[-1]

    ## select the highest value for every area_id and assign the value '1' to its 'highest' field
    where2 = """{} = '{}' AND {} = {}""".format("area_id", i, "height", max_height) # if area_id is text.
#    where2 = """{} = '{}' AND {} = {}""".format("area_id", i, "height", max_height) # if area_id is numeric.
    with arcpy.da.UpdateCursor(points, ["highest"], where2) as cur3:
        for row3 in cur3:
            row3[0] = 1     # assumes the format of the 'highest' field is numeric (short)
            cur3.updateRow(row3)
            break   # this will tag only 1 of the highest points for every area_id.
                    # If you want to tag all highest points, remove this line.