SQL query expression in related table

1024
3
05-26-2021 04:26 AM
luxairport_rim
New Contributor

Good afternoon

I have a feature of points <Points_defauts>  that has a related table, used to store the evolution of a certain object

I am trying to do a table join in ArcMap, in order to obtain the latest record.

I started to do a definition query in the related table in order to get the latest value collected, an then do the table join.

 

In my related table I have has an external Key field [Point_ID] and a field date [CreationDate].

I just want to have the latest record, based on the date field.

In made some google and I found this query:

 

[POINT_ID],[CREATIONDATE] IN

(SELECT [POINT_ID],[CREATIONDATE]

FROM TABLE AS table1

GROUP BY [POINT_ID]

HAVING COUNT([POINT_ID]) > 1)

 

However, an expression error is the only result I have.

 

Is this expression possible in ArcMap?

What I am doing wrong??

Sorry, but I’m newbie in sql expressions…

luxairportluxairport_0-1622028364033.png

 

 

0 Kudos
3 Replies
JoshuaBixby
MVP Esteemed Contributor

If you haven't done so already, I suggest you read About joining and relating tables—ArcMap | Documentation (arcgis.com).

You mention "my related table," have you set up a table relation in the software between the two tables, or are you just talking about two tables that are related logically but you haven't created a formal relation between them in ArcMap?

0 Kudos
luxairport_rim
New Contributor

I have done the relationship 1:N in the geodatabase correctly, with intern and external key. 
my problem is after… I have several rows with same ID with different dates

i want make a query view in the related table that give me only the last register based on date

in AGOL it is possible, but I need to have the same  function in ArcMap

0 Kudos
luxairport_rim
New Contributor

Help please.. can anyone help me?

0 Kudos