SQL Query Most Recent Record

Question

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

AndresCastillo · Answer

Hi William,

can you help me put my question into this context?

See my question:

https://community.esri.com/thread/215046-symbolizing-feature-class-using-related-table-values

WilliamCraft · Answer

Is the data stored in Oracle, SQL Server, or some other data store?  From where are you issuing this query?  In the Select By Attribute dialog box of ArcMap, a database management tool, or somewhere else?  You might have better luck using a Query Layer depending on your requirements.  Here are a few potential ways that I can think of:

-- Queries which can be issued from an database management tool or from a Query Layer in ArcMap:SELECT
  FIELD_ID,
  MAX(OBSERVATION_DATE)
FROM TABLE
GROUP BY FIELD_ID, OBSERVATION_DATE;

SELECT * FROM 
  (SELECT * FROM TABLE ORDER BY OBSERVATION_DATE DESC ) table1 GROUP BY FIELD_ID ';

-- Query Expression which may work from the Select By Attribute dialog box or in a Layer Definition within ArcMap:[FIELD_ID],[OBSERVATION_ID] IN 
(SELECT [FIELD_ID],[OBSERVATION_ID]
FROM TABLE AS table1 
GROUP BY [FIELD_ID] 
HAVING COUNT([FIELD_ID]) > 1)

note: the brackets above may need to change to double quotes