Selecting one row from each group based on a Varchar field

Question

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?

Answer 1

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

Answer 2

You could create a cte that gets the avengers that meet the type criteria, and then union that with a select of the avengers that don't meet the type criteria, but are distinct. Based on your example, this is what I think you are looking for.

;with avengerCTE as (
SELECT ID, Avenger, Type, Power 
FROM Avenger
WHERE type = 'bar')

SELECT * FROM avengerCTE
UNION 
SELECT * FROM Avenger a
WHERE NOT EXISTS(SELECT 1 FROM avengerCTE ac WHERE a.avenger = ac.avenger)     

Answer 3

A self-join will work:

Select 
  F.avenger,
  Coalesce (b.type, f.type),  --return the "bar" in preference to the "foo"
  coalesce (b.whatever, f.whatever)
  .. more columns
From Avenger as f             -- always exists
left outer join Avenger as b  -- optional, hence OUTER
  On f.Avenger = b.Avenger
  and b.type = 'bar'
where F.type = 'foo';

This becomes cumbersome as the number of preferences increases, however.