Select particular result randomly from a table for a certain partition

Question

I want to select record corresponding to 'B' whenever there are duplicates for a name. If there's no duplicate I want to display the record. Refer to the sample table [TableInfo]. Please help me with the SQL query.

TableInfo

Name    Type    Value
------------------------
Name1   A       5
Name1   B   10
Name1   C   11
Name5   B   88
Name5   C   98
Name6   A   24
Name6   B   21
Name2   B   21
Name3   C   55
Name4   A   74

The expected result:

Name    Type    Value
------------------------
Name1   B   10
Name5   B   88
Name6   B   21
Name2   B   21
Name3   C   55
Name4   A   74

Answer 1

I think you want this:

select i.*
from info i
where type = 'B'
union all
select i.*
from info i
where not exists (select 1 from info i2 where i2.name = i.name and i2.type = 'B');