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
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?