Query to select MIN and MAX values by groups

Anonymous User

Original User: AntonioH

In ArcMap 10 i'm working in a Personal Geodatabase where the points (see screenshot below) represent the number of each house in the street. Each point has an ObjectID. This ObjectID is an unique value for each "link" of the street to which all the points are related.

[ATTACH=CONFIG]34858[/ATTACH]

I have a total of 35,000 points (house numbers), which are all related to 14,000 different links. What I want is to select both the MAX and MIN value in each side of the street. Each side of the street also has a unique ID, which appears in my attribute table as CVEFT. On every link, the CVEFT (for both sides) is different.

This is what my Table looks like:

[ATTACH=CONFIG]34859[/ATTACH]

As you can see in the screenshot above, there are several points (NUMEXT) related to the same link (OBJECTID). You can also see that all the points related to a single link may have 2 different street-side codes (CVEFT).

I want to build a SQL query that allows me to select the MIN and MAX values for every Link, for each side of the street.

Does anyone have any idea of how to do this? Any ideas would be appreciated! Thanks!

sc2.jpg

sc1.jpg

jskinner

One way to achieve this is through python.  Below is example:

import arcpy
from arcpy import env
env.overwriteOutput = 1
#Set workspace
env.workspace = r"C:\temp\python\test.gdb"

table = "Addresses"

list = []

#select all OBJECTIDs and  append to list
with arcpy.da.SearchCursor(table, ["OBJECTID"]) as cursor:
    for row in cursor:
        list.append(row[0])

del row, cursor

#remove duplicates from list
list = dict.fromkeys(list)
list = list.keys()

list2 = []
#select all CVEFT values based on unique OBJECTID
for n in list:
    with arcpy.da.SearchCursor(table, ["CVEFT"], "OBJECTID = " + str(n)) as cursor:
        for row in cursor:
            list2.append(row[0])

del row, cursor

#remove duplicate CVEFT values
list2 = dict.fromkeys(list2)
list2 = list2.keys()

arcpy.MakeFeatureLayer_management(table, "fc_lyr")

#select all NUMEXT values based on unique CVEFT values
for n in list2:
    list3 = []
    with arcpy.da.SearchCursor(table, ["NUMEXT"], "CVEFT = " + str(n)) as cursor:
        for row in cursor:
            list3.append(row[0])   
    #sort list     
    list3.sort()
    #select max NUMEXT
    arcpy.SelectLayerByAttribute_management("fc_lyr", "ADD_TO_SELECTION", "NUMEXT = " + str(list3[-1]))

del row, cursor

#export selection to feature class
arcpy.FeatureClassToFeatureClass_conversion("fc_lyr", env.workspace, "Selection")