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