关于数据去重的问题

[时光是琥珀]

ID subnumber type
1 a 1
2 a 6
3 b 1
4 b 1
5 c 1
6 d 1
我有张数据表，表简要结构如上，我最终想要的数据是
ID subnumber type
1 a 1
2 a 6
4 b 1
5 c 1
6 d 1
type为1的subnumber有重复项，我想只剔除type为1的subnumber的重复项，请问下大家该如何处理

[道素]

if OBJECT_ID('tempdb..#a') is not null drop table #a
  ;with a(ID,subnumber,type) as (
 
     select 1,'a',1 union all
     select 2,'a',6 union all
     select 3,'b',1 union all
     select 4,'b',1 union all
     select 5,'c',1 union all
     select 6,'d',1
)
 select * into #a from a
 delete a from #a as a 
 where a.ID in(
    select t.id from (select *,ROW_NUMBER()over(partition by subnumber,type order by id desc) as rn from #a) t where rn>1
)
select * from #a
/*
ID	subnumber	type
1	a	1
2	a	6
4	b	1
5	c	1
6	d	1
*/

[xxfvba]

select id,subnumber,type from (select *,rn=row_number() over (partition by subnumber , type order by id desc) from T) a where rn=1

[Ginnnnnnnn]

;WITH CTE (ID,subnumber,TYPE)
AS(
select 1,'a',1
Union ALL select 2,'a',6
Union ALL select 3,'b',1
Union ALL select 4,'b',1
Union ALL select 5,'c',1
Union ALL select 6,'d',1
Union ALL select 7,'d',2
Union ALL select 8,'d',2
)
SELECT * 
	FROM CTE a
	WHERE NOT EXISTS(SELECT * FROM CTE WHERE TYPE = 1 AND CTE.subnumber = a.subnumber AND ID > a.ID)