create table #tblAccessList
(SecGrp varchar(100),
 Access varchar(10))
 
 insert into #tblAccessList values ('AdmAccess','Modify')
 insert into #tblAccessList values ('AdmAccess','Read')
 insert into #tblAccessList values ('AcctAccess','Read')
 insert into #tblAccessList values ('FinAccess','Modify')
 insert into #tblAccessList values ('DevAccess','Modify')
 insert into #tblAccessList values ('DevAccess','Read')
 insert into #tblAccessList values ('ProdAccess','Modify')
 insert into #tblAccessList values ('TestAccess','Read')

DELETE t
FROM   #tblAccessList t
WHERE  EXISTS 
       (
           SELECT *
           FROM   #tblAccessList t2
           WHERE  t2.SecGrp = t.SecGrp
                  AND t2.Access = 'Modify'
       )
       AND t.Access = 'Read';

Delete F from
(Select *,ROW_Number() OVER (PARTITION By SecGrp Order by Case When Access = 'Modify' then 1 Else 2 End) as Seq
 from #tblAccessList
 )F
 Where F.Seq > 1

quote:

---

Delete F from
(Select *,ROW_Number() OVER (PARTITION By SecGrp Order by Case When Access = 'Modify' then 1 Else 2 End) as Seq
from #tblAccessList
)F
Where F.Seq > 1

---