SQL Query Most Recent Record

Discussion created by acr34 on Mar 17, 2014
Latest reply on May 18, 2018 by andrescastillo08
Wondering if someone could point me in the right direction? I'm working with a ArcSDE database that contains data associated with points that all have a unique id. Every year we return to these points and collect additional data. The spatial data associatied with the points can change from year to year but the ID stays the same. When we add each year to the DB it adds it as a new record. So we will have multiple records for the same ID with differing years. I am trying to query the most recent records for the unique Id using
OBSERVATION_DATE = (SELECT MAX( "OBSERVATION_DATE") FROM Table) but that only gives me the most recent data based on the date but I'm trying to get the most recent record for each unique ID (or the most recent in an individual year such as 2013). I'm sure there is a way to do this using a subquery but cant figure it out. Any help would be appreciated