Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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

Any ideas would be appreciated!

enter image description here

share|improve this question
2  
Something is amiss with your quotes... what DBMS is this on? –  blah238 Apr 25 '12 at 16:31
    
Thanks @blah238 the data is contained in a Shapefile which is being queried by a internal query builder within ARCGIS 10. The double quotes work fine in other queries on same plateform. –  user6784 Apr 25 '12 at 17:19
    
I also believe it is the quotation marks. Can you show a sample of the data? –  bspencer Apr 25 '12 at 18:24
    
Data is actually stored within a file geodatabase. –  user6784 Apr 25 '12 at 20:28
    
data is posted as an image. I also copied the data to a personal geodatabase. I tried another version of the statement as brackets are now the default instead of quotes. [TL] IN (SELECT max( [TL] ) FROM wstest GROUP BY [Site_ID] ). I tried a few variations of qoutes or brackets around the table name wstest. Using nothing and also square brackets produced no syntax errors but did not limit the records. –  user6784 Apr 25 '12 at 20:52

2 Answers 2

up vote 2 down vote accepted

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

share|improve this answer

The only mention of MAX in the ArcMap SQL Help files relates to Subqueries (nested queries). Not sure if it works for what you're trying, but the syntax used is ALL CAPS.

This query would return the features with a GDP2006 greater than the GDP2005 of any of the features contained in countries:

"GDP2006"(SELECT MAX("GDP2005") FROM countries)

For each record in the table, a subquery may need to parse all the data in its target table. It may be extremely slow to execute on a large dataset.

share|improve this answer
    
Changing the Max to MAX still produced the same error. Also the statement that I used was given at support.esri.com/en/knowledgebase/techarticles/detail/31843 but does not work. –  user6784 Apr 25 '12 at 17:24
1  
@user6784 - It says specifically in the ESRI link you provided that shapefiles do not support subqueries. Maybe that's the issue? –  Baltok Apr 25 '12 at 18:36
    
@Baltok - I checked and the data is located within a file geodatabase. –  user6784 Apr 25 '12 at 20:24
1  
A file geodatabase has the same restriction as a shapefile in regards to SQL subqueries. You can export your data to a personal or SDE geodatabase and your query will probably work then. –  Baltok Apr 25 '12 at 20:28
    
Thanks for this @Baltok, I have copied to personal geodatabase and I get no syntax error but the same number of records are being returned. –  user6784 Apr 25 '12 at 20:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.