I have a dataset of end points of some stream lines. I want to select the points that are lowest in elevation out of each set of endpoints.
The attributes of the point dataset include "elevation" of each point and a number "stream" representing the stream it came from. So I'm looking for something like: select the point with minimum "elevation" from each pair of points that have the same "stream" value
The attributes of the point dataset include "elevation" of each point and a number "stream" representing the stream it came from. So I'm looking for something like: select the point with minimum "elevation" from each pair of points that have the same "stream" value
If this is an Access database or an SDE database you might get it to work, but you need to look at the help specific to that database and get it to work within the origin program first. I am not sure that even they support such queries without a separate external table holding the min values for each group. (I.e., in Access I have to do a Make Table Query base on the Min of each group and then run the query against the main table for this to work).
If this is a shapefile or a File Geodatabase, grouped subqueries that respect each group are not supported, since as you said the group by operator will always find the list of every elevation that is a min of any of the groups and then the selection will match any elevation in that list regardless of whether it is in correct the group or not. To do this efficiently programatically you need a cursor and a dictionary in Python to maintain a list that pairs the correct unique streamID with each min elevation for that group, or you need to run Summary Statistics and create a Summary Table with the min elevation for each unique streamID case and join it to your full table on the streamID to be able to select min elevations that respect a group. This information came from the programmer who wrote the Shapefile and File Geodatabase SQL specifications.