Selecting one row from each group based on a Varchar field

Given a table Avenger whose data looks like this:

id  Avenger         Type    Power
1   Captain America foo      2
2   Captain America bar      3
3   Me              foo      5
4   You             foo      1
5   You             bar      7
6   Iron Man        foo      4

I want to return a distinct result set of the Avengers, although all other data in the table is relevant to my processing interests. For business reasons I want to return Type "bar" type records instead of "foo" where applicable.

I can do this in two steps, certainly:

declare @avenger table
(
    id int,
    Avenger varchar(50),
    [Power] varchar(50),
    [Type] varchar(50)
)

insert into @avenger
select * from Avenger where Type = 'bar'

insert into @avenger
select t.ID, t.Avenger, t.Type, t.Power from Avenger as t
where t.Avenger not in (select Avenger from @avenger)

And then just return my result set from @avenger. Because I need the other fields from Avenger (e.g. Power) a UNION seems out of the question. Type is varchar data and while there are 2 now there might be more in the future, so I'm uncertain about trying to sort it based on alpha or anything like that. Is there some better/preferred way to do this?

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

StackExchange Database Administrators Q#137260, answer score: 6