title: How do i get my MAX SQL Select statement to work within my inner join
body: <p>I've wrote a small inner join that takes column's from two different database tables; WKS_LOG_VIEW and FDE_XML_VIEW. I match them on the ACID/AID and data is returned within the date and machine selected (this works). I have then tried to add a MAX select query that will only put the latest EventTime row in. As i only want one record per EventTime entry. I don't need many EventTime entries just the latest. My query is returning no entries.</p>
  <pre><code>select 
  EXCDS1.dbo.FDE_XML_VIEW.ACID,
  EXCDS1.dbo.FDE_XML_VIEW.SID,
  EXCDS1.dbo.FDE_XML_VIEW.STAR,
  EXCDS1.dbo.FDE_XML_VIEW.ROUTE, 
  EXCDS.dbo.WKS_LOG_VIEW.AID,
  EXCDS.dbo.WKS_LOG_VIEW.Sector,
  EXCDS.dbo.WKS_LOG_VIEW.LocalCoAltIn
  EXCDS.dbo.WKS_LOG_VIEW.EventTime
  from EXCDS1.dbo.FDE_XML_VIEW
  inner join
  EXCDS.dbo.WKS_LOG_VIEW 
  on AID = ACID
  where
  LastUpdateMachine = 'R02'
  and
  convert (date, EventTime) = '2021-03-11'
  and
  EXCDS.dbo.WKS_LOG_VIEW.EventTime = (SELECT MAX(lv.EventTime) from EXCDS.dbo.WKS_LOG_VIEW as lv
  where
  lv.AID = EXCDS.dbo.WKS_LOG_VIEW.AID)
  order by ACID, EventTime;
  </code></pre>
  <p>I'm very new to SQL so apologies if I'm making obvious mistakes.</p>