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

• XIY: For the ArcMap method, see: gis.stackexchange.com/questions/29372/…
• Reza: I think the sql query missed the name of Area_ID field.Did you try this query? : ( Select Aread_Id,Max(Height) As MaxHeightInThisArea from elev_p group by Area_Id).
• jonaktiv: thanks, But that only gives me the values of maximum height, I need to select them so I know wich features it is that have the maximum height
• Joseph: If you have found a solution to your problem, you should post it as an answer (and then accept it 2 days after it was first posted) instead of adding "[SOLVED]" in the title.
• dassouki: If you can use ArcPy or python in the data. Loading it into a list then doing a for loop makes it quiet easy. data = [(100,1), (200,1), (300,2), (500,2), (100,1), (400,3), (400,2), (200,1), (200,4)] d = {} for former, latter in data: d[former] = max(latter, d.get(former, 0))

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.

• jonaktiv: Great! That means I can do it in Arcmap, however I never used arcpy, python. If you have time, can you write this python script with the scenario I have created with the change that I have a field called highest, and then calculate highest = 1 if that is the heighest height within AREAid? You have explained well but I feel just like a dummy

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.

• jonaktiv: THANK YOU! I will test and get back to you, and now I will also add a comment to do this the boring way. since summarize destorys objectid you can dissolve your layer based on AREAid then statistics HEIGHT maximum, then join the layer to the original feature class, and then select by attributes, dissolve_height = HEIGHT AND dissolve_Areaid = AREAid.