SQL Server列一相同的情况下，如何取列二的最大值，并重新生成表

我有一个数据库的格式如下：
SourceName TimeStamp Status Path1 Path2
Alarm1 20220101 1 Street1 Street1_1
Alarm1 20220102 0 Street1 Street1_1
Alarm1 20220103 1 Street1 Street1_1
Alarm2 20220101 0 Street1 Street1_1
Alarm2 20220301 1 Street1 Street1_1
Alarm3 20220301 1 Street1 Street1_1
Alarm3 20220501 1 Street1 Street1_1
第一步筛选出下面的表
SourceName TimeStamp Status
Alarm1 20220101 1
Alarm1 20220102 0
Alarm1 20220103 1
Alarm2 20220101 0
Alarm2 20220301 1
Alarm3 20220301 1
Alarm3 20220501 1
第二步，在列一相同的情况下，取出列二的最大值
期待结果
SourceName TimeStamp Status
Alarm1 20220103 1
Alarm2 20220301 1
Alarm3 20220501 1

想知道这样的筛选应该怎么写SQL语句，谢谢

select t1.* from test1 as t1
right join (select SourceName ,max(TimeStamp ) as TimeStamp  from test1 where Status = '1' group by SourceName) as t2 on t1.SourceName  = t2.SourceName  and t1.TimeStamp  = t2.TimeStamp 

select SourceName  , max(TimeStamp ) as TimeStamp , max(Status) from table_name  group by SourceName  

SELECT a.SourceName,a.TimeStamp,a. Status from table a right JOIN (SELECT MAX(SourceName) as SourceName from table GROUP BY SourceName) b on a.SourceName = b.SourceName

SELECT a.*
FROM m a,(SELECT SourceName,MAX(TimeStamp) as TimeStamp FROM m GROUP BY SourceName) b
WHERE a.TimeStamp = b.TimeStamp
AND a.SourceName = b.SourceName;