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
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?