here when same vname,same customerid,but if there are two or more vid,then we will keep one and delete the others.
Here's the conditions--two or more vid having same vname and same customerid,then look for max sellid, if its null then look for max aptid,if its null then look for minimum vid. sellid and aptid will not be same for two different vid's.
create table #Test(vname varchar(10),customerid int,vid int,sellid int,aptid int)
insert into #Test(vname,customerid,vid,sellid,aptid)
select 'ZFF65TJ' as vname,13823 as customerid,18304 as vid,6554 as sellid,null as aptid
union all
select 'ZFF65TJ' as vname,13823 as customerid,18304 as vid,6554 as sellid,null as aptid
union all
select 'ZFF65TJ' as vname,13823 as customerid,18304 as vid,null as sellid,3209 as aptid
union all
select 'ZFF65TJ' as vname,13823 as customerid,18305 as vid,null as sellid,3207 as aptid
union all
select 'ZFBCFAD' as vname,15625 as customerid,9602 as vid,1234 as sellid,3205 as aptid
union all
select 'ZFBCFAD' as vname,15625 as customerid,9603 as vid,null as sellid,3206 as aptid
union all
select 'KNDJF72' as vname,14165 as customerid,5571 as vid,null as sellid,null as aptid
union all
select 'KNDJF72' as vname,14165 as customerid,5585 as vid,null as sellid,null as aptid
select * from #Test
order by vname,sellid desc,aptid desc,vid asc
select * from #Test
order by vname,sellid desc,aptid desc,vid asc
desired results--
vname customerid vid sellid aptid
KNDJF72 14165 5571 NULL NULL
--here sellid and aptid is null for both vid (5571 and 5585),so we will keep minimum vid 5571 and delete 5585
ZFBCFAD 15625 9602 1234 3205
--9602 vid has sellid so we will keep this and delete 9603
ZFF65TJ 13823 18304 6554 NULL
--18304 and 18305 has both sellid null,but 18304 aptid is greater than 18305 vid so we will keep 18304 and delete 18305
Thanks in advance