CODEWITHSUNDEEP
sqlsql-server
ZZBeard
ZZBeard

Reputation: 29

How do i get my MAX SQL Select statement to work within my inner join

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.

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;

I'm very new to SQL so apologies if I'm making obvious mistakes.

Upvotes: 1

Views: 87

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

I think the issue is the additional filtering in the query. The simplest way to solve this uses row_number():

select fw.*
from (select f.ACID, f.SID, f.STAR, f.ROUTE, 
             wl.AID, wl.Sector, wl.LocalCoAltIn wl.EventTime,
             row_number() over (partition by wl.AID order by wl.EventTime) as seqnum
      from EXCDS1.dbo.FDE_XML_VIEW f join
           EXCDS.dbo.WKS_LOG_VIEW wl
           on wl.AID = f.ACID
      where LastUpdateMachine = 'R02' and
            convert(date, wl.EventTime) = '2021-03-11'
     ) fw
where seqnum = 1;
order by ACID, EventTime;

Notes:

  • Use tables aliases. They make the query easier to write and to read.
  • Qualify all column references. I still have no idea what table LastUpdateMachine comes from.
  • Your query seems to reference two views not two tables. This doesn't matter for your query. I just want to be sure that you understand the difference between a view and a table.

Upvotes: 1

Related Questions