Remove/retain record based on grouping

Transact-SQL
1

Hi,

I have the following data:

declare @Policy table
(
Download varchar(20),
AccountNum varchar(20),
RootPolicyNum varchar(20),
PolicyNum varchar(20),
CurrentStatus varchar(20)
)

insert into @Policy (Download, AccountNum, RootPolicyNum, PolicyNum, CurrentStatus)
select 'EQAutralia', '57126', '1006784', '1006784', 'Renewed'
UNION ALL
select 'EQAutralia', '57126', '1006784', '1000194', 'Approved'
UNION ALL
select 'EQAutralia', '57126', '1006784', '00XH09000', 'Approved'
UNION ALL
select 'EQAutralia', '57126', 'AU120736', 'AU120736', 'Approved'
UNION ALL
select 'EQCalifornia', '57126', '1006784', '00XG96301', 'Renewed'

select * from @Policy

For every group of Download, AccountNum, and RootPolicyNum, I only want to keep the 'Renewed' CurrentStatus records if they are the only row in a group. So in the example data above, I want to remove the first row (EQAustralia, 57126, 1006784, 1006784, Renewed) since it is part of a group that containins 'Approved' records, but I want to keep the last row (EQCalifornia, 57126, 1006784, 00XG96301, Renewed) because it is the only record for that group and it is CurrentStatus is 'Renewed'

thanks
Scott

2 
SELECT  *
FROM    @Policy p1
WHERE   p1.CurrentStatus <> 'Renewed'
        OR NOT EXISTS 
        ( 
        SELECT  
			*
        FROM    @Policy p2
        WHERE   p2.Download = p1.Download
                AND p2.AccountNum = p1.AccountNum
                AND p2.RootPolicyNum = p1.RootPolicyNum
                AND p2.CurrentStatus <> 'Renewed' 
        );
3

works, thanks!