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

Question

我有一个数据库的格式如下：
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语句，谢谢

Answer 1

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