sql server - t-sql complex query to get only maximum status with 'R' -

i want maximum claim status version status type 'r' , if there other status same claim id want filter those.

below query doesn't give me required results.

select p.prov_clm_id,p.prov_clm_stat_type,max(p.clm_stat_version) provider_clm_stat p  p.provider_clm_stat_type='r'  group p.prov_clm_id,p.provider_claim_status_type   194 r   1 231 r   1 469 r   1 649 r   1 

if there other claim status don't want show in results.

select * provider_clm_stat prov_clm_id=194   194 5   b 194 2   k 194 3   g 194 4   q 194 7   h 194 8   p 194 1   r 194 6   x   required results:  740 r   1 

if want claim status have 'r' , no other statuses:

select p.prov_clm_id, p.prov_clm_stat_type, max(p.clm_stat_version) provider_clm_stat p  group p.prov_clm_id, p.provider_claim_status_type having max(p.provider_clm_stat_type) = 'r' ,        min(p.provider_clm_stat_type) = 'r'; 

the filtering here in having clause after aggregation. if min() , max() values both 'r', values 'r' or null. if null possibility , want filter out, use clause:

having max(p.provider_clm_stat_type) = 'r' ,        min(p.provider_clm_stat_type) = 'r' ,        count(*) = count(p.provider_clm_stat_type)