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?

3 Answers 3


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

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 Avenger a
WHERE NOT EXISTS(SELECT 1 FROM avengerCTE ac WHERE a.avenger = ac.avenger)     

A self-join will work:

  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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.