ArcGIS Definition Query – Selecting Max Value from Group Using ArcGIS Definition Query

arcgis-10.0arcgis-desktopdefinition-querysql

In ArcGIS 10 I am trying to do a Definition Query that will select the records that are the max values from a specific group.

Here is what I have tried in the Query Builder but I am getting an "the SQL statement is invalid." error.

"TL" IN (SELECT max( "TL" ) FROM "Instrument_Locations2_20120227" GROUP BY "Site_ID" )

enter image description here

Best Answer

This was solved by keeping data in personal geodatabase and modifying the statement to: [TL] <> 0 AND [TL] IN (SELECT MAX( [TL] ) FROM wstest GROUP BY [Site_ID] )

Related Solutions

ArcGIS Python – Add Definition Query to Layers in a Group in ArcGIS 10.0

Okay I got it, thanks anyways! It's a little bit more elegant now.

import arcpy

#Variables to form defintion query
field = '"Country"'
value = "'Canada'"
#concatenate query syntax
queryStr = str(field) + "=" + str(value)
#Specify the MXD project (CURRENT), dataframe (Layers)
mxd = arcpy.mapping.MapDocument("CURRENT")
df = arcpy.mapping.ListDataFrames(mxd, "Layers")[0]
#Apply defintion query to specified layer group (Test)
for lyr in arcpy.mapping.ListLayers(mxd, "Test", df)[0]:
    if lyr.supports("DEFINITIONQUERY"): 
        lyr.definitionQuery = queryStr
arcpy.RefreshActiveView()       
del mxd

ArcGIS 10.1 – Using SQL on Definition Query to Select Minimum Value

"Air_Mil" in (SELECT MIN( "Air_Mil" ) FROM Polygone_Buffer_Output

[Air_Mil] in (SELECT MIN( [Air_Mil] ) FROM Polygone_Buffer_Output

using square brackets or quotes depends on your underlying database implementation.

http://resources.arcgis.com/en/help/main/10.1/index.html#//00s500000033000000

Best Answer

Related Solutions

ArcGIS Python – Add Definition Query to Layers in a Group in ArcGIS 10.0

ArcGIS 10.1 – Using SQL on Definition Query to Select Minimum Value

Related Question