Display the latest value (eg mobile no)from the active record of customers

1

i am beginner in sql, Display the latest value (eg mobile no)from the active record of customers
below is the Source for one customer if i have many, how to get the latest value, we need to ignore the "D" record and select the 2nd record.

Custno inserttype mobileno effdt
123 D 899999 2020-01-01
123 I 898797 2019-10-10
123 U 546789 2019-03-24
123 I 744444 2018-01-01
345 U 616161 2020-01-01
345 I 666666 2018-01-01

output
Custno inserttype mobileno effdt
123 I 898797 2019-10-10
345 U 616161 2020-01-01

please help me with query

2 
use sqlteam
go

declare @moombaka table(Custno int, inserttype char(1), mobileno int, effdt date)

insert into @moombaka
select 123 ,'D', 899999, '2020-01-01' union 
select 123 ,'I', 898797, '2019-10-10' union 
select 123 ,'U', 546789, '2019-03-24' union 
select 123 ,'I', 744444, '2018-01-01' union 
select 345 ,'U', 616161, '2020-01-01' union 
select 345 ,'I', 666666, '2018-01-01' 

;with src
as
(
select Custno, inserttype, mobileno, effdt ,
ROW_NUMBER() OVER (
      PARTITION BY Custno
      ORDER BY effdt desc
   ) row_num
From @moombaka 
where inserttype <> 'D'
)
select * From src where row_num = 1
3

or


;with src
as
(
select Custno, max(effdt) maximus
From @moombaka 
where inserttype <> 'D'
group by Custno
)
select * 
  From @moombaka  a
  join src on a.Custno = src.Custno and effdt = maximus
1 Like