5

I would like to use a MAX SQL query for a column in a definition query, where there records that are being queried are the MAXIUM from the field UPDATE_DATE_DATE while grouping by the field NUMBERCYLA. I am using this in an an SDE enabled geodatabase on SQL server 2008.

Here is what I have so far [UPDATE_DATE_DATE] in (SELECT max( [UPDATE_DATE_DATE] ) FROM SO_SC_1 GROUP BY [NUMBERCYLA])

  • 1
    What is the type of your database (FGDB, Enterprise,...)? please include it in your question – Farid Cheraghi Sep 30 '15 at 17:21
  • Probably better off creating a view, and accessing that. – Vince Sep 30 '15 at 18:22
  • If the solution below isn't working, what result are you getting? – Tom Sep 30 '15 at 20:42
  • Remove the brackets from field names (Like Tom's suggestion) – Farid Cheraghi Sep 30 '15 at 21:03
2

Since you're on SDE, you can achieve what you're asking with:

UPDATE_DATE_DATE IN (SELECT MAX(UPDATE_DATE_DATE) FROM SO_SC_1 GROUP BY NUMBERCYLA)

As reference for others who may come across this in searching for a file geodatabase solution:

Note that file geodatabases are extremely limited with subqueries. See: http://resources.arcgis.com/EN/HELP/MAIN/10.2/index.html#/SQL_reference_for_query_expressions_used_in_ArcGIS/00s500000033000000/

If you were just trying to retrieve the overall maximum and it's in a file geodatabase or SDE:

UPDATE_DATE_DATE = (SELECT MAX(UPDATE_DATE_DATE) FROM SO_SC_1)

If it's in a personal geodatabase, then you need to move into the 21st century. J/K. see: https://gis.stackexchange.com/a/26750/10919

If you're interested in getting the maxima grouped by some other field and your feature class is in an FGDB, then you're out of luck. You will need to implement a work-around in which you identify, select, and export the features to a new feature class.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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