6

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

Improve this question
7
  • 2
    Something is amiss with your quotes... what DBMS is this on?
    – blah238
    Apr 25, 2012 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, 2012 at 17:19
  • I also believe it is the quotation marks. Can you show a sample of the data?
    – bspencer
    Apr 25, 2012 at 18:24
  • Data is actually stored within a file geodatabase.
    – user6784
    Apr 25, 2012 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, 2012 at 20:52

2 Answers 2

Reset to default
2

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

Improve this answer
1
1

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.

Improve this answer
7
  • 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, 2012 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, 2012 at 18:36
  • @Baltok - I checked and the data is located within a file geodatabase.
    – user6784
    Apr 25, 2012 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, 2012 at 20:28
  • 2
    @Chris W- I stand corrected in the blanket statement about shapefile/gdb subquery support. However, since gdb support for MAX only works in scalar subqueries, you cannot use the same syntax as mdb and sde geodatabases. I still recommend if you are building semi-complicated SQL queries, you are better off going with mdb or sde geodatabases. YMMV.
    – Baltok
    Apr 2, 2015 at 21:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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