Group by with max date help

Hi,

I have these 2 tables. I want to update the testtbl3 with mid values with a join from testtbl4 mid values.

in testbl4, there are 2 different values for npi2. I want the mid with latest effectivedate from testtbl4. How can I achieve this? I think i will need some type of group by and max date. Tried but not able to figure it out.

Tables:

create table operationsdb.ops.testtbl3 (npi varchar(20), mid varchar(20))

insert into operationsdb.ops.testtbl3 values ('npi1', 'placeholder')
insert into operationsdb.ops.testtbl3 values ('npi2', 'placeholder')
insert into operationsdb.ops.testtbl3 values ('npi3', 'placeholder')

create table operationsdb.ops.testtbl4 (npi varchar(20), mid varchar(20), effectivedate date)
insert into operationsdb.ops.testtbl4 values ('npi1', '024894600','2018-01-17')
insert into operationsdb.ops.testtbl4 values ('npi2', '008520600','2012-04-19')
insert into operationsdb.ops.testtbl4 values ('npi2', '046082600','1987-05-31')
insert into operationsdb.ops.testtbl4 values ('npi3', '009595700','2013-09-16')

Expected result:

npi mid
npi1 024894600
npi2 008520600 -- there are 2 entries in testtbl4 for npi2 but i want the mid with latest date.
npi3 009595700

Thanks a lot! any help is greatly appreciated.

select t3.npi, t4_last.mid
from #testtbl3 t3
outer apply (
    select top (1) *
    from #testtbl4 t4
    where t4.npi = t3.npi
    order by effectivedate desc
) as t4_last
1 Like

Thanks Scott, I will test it out as well.

In the mean time, I figured it out as well.

;with cte as
(select npi, max(effectivedate) as dateofint from OperationsDB.ops.testtbl4
group by npi
)

update tbl3 set mid = tbl4.mid
from OperationsDB.ops.testtbl3 tbl3, OperationsDB.ops.testtbl4 tbl4, cte
where tbl3.npi = tbl4.npi and tbl4.npi = cte.npi and tbl4.effectivedate = cte.dateofint

This gives me the desired result.

Just be aware that if you have two rows that happen to have the same last effectivedate, that method will return duplicate rows to the query, the other method will not.

sure top 1 is more elegant way. i did not know about outer apply mechanism. always something to learn!

Thanks Scott.