SQLTeam.com | Weblogs | Forums

Select duplicates where atleast one meets condition

Hi,

I have a table with duplicates based on their name.

select fname, mname,lname, count()
from mni
group by fname, mname, lname
having count(
) >1

Of these duplicate records I would like to know ones have atleast one of the rows with a field value of i_leo = '1'.

Both of the rows could have it, or just one. Doesn't matter.

Thanks so much for your help. I appreciate it.

select fname, mname, lname, count(*) as [dup_name_count]
from mni
group by fname, mname, lname
having count(*) >1 and max(case when i_leo = '1' then 1 else 0 end) = 1