AnsweredAssumed Answered

How to use SQL aggregate functions in a definition query ?

Question asked by jason.carter@waterboards on Sep 10, 2019
Latest reply on Sep 10, 2019 by bixb0012

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? 

Outcomes