Selecting one row from each group based on a Varchar field
You can do something like this:

select * from (
  select *, row_number() over (partition by Avenger order by Type) as RN
) X
where RN = 1


This will number the rows for each avenger, and for your actual business case you'll most likely need to use a case statement in the order by if there's more than 2 different values, to define their precedence:

....order by case Type when 'bar' then 1 when 'foo' then 2 else 3 end