How to use SQL aggregate functions in a definition query ?

JasonCarter1

Hi,

I have a feature class of point data in a file geodatabase. The points include a location_id, date, and a chemical_result. Because there is more than one result, the points are displayed in duplicate. I do not want to use a related table in this instance - I just want one point to display the maximum result, and the date of each max result result. 

I thought I would be able to write a definition query as:

LOCATION_ID IN (SELECT LOCATION_ID, DATE, MAX(CHEMICAL_RESULT) FROM layer_name

                              GROUP BY LOCATION_ID);

I also tried...

SELECT x.DATE, x.LOCATION_ID, y.max_result
FROM layer_name x
INNER JOIN (SELECT LOCATION_ID, MAX(CHEMICAL_RESULT) max_result
FROM layer_name
GROUP BY LOCATION_ID
) y ON x.LOCATION_ID = y.LOCATION_ID

However I receive an invalid SQL error. 

How can I get the max result, the location ID, and the date of the max result? 

bixb0012

If your data is in a file geodatabase, your approach won't work.  First off, your SQL is invalid in several DBMSs, not just file geodatabases.  What you would need to do, instead of using SQL IN, is use a correlated subquery using SQL EXISTS.  Unfortunately, file geodatabases don't properly support correlated subqueries with EXISTS, so you are out of luck going down the definition query path.  See https://community.esri.com/thread/169714 for more discussion.