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
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))