In a UNION Statement, is the record from the first SELECT the survivor?

Question

I have two mailing addresses each with a different purpose. Some have a "M"ailing address...some have a "D"efault address. If a record has both, I want the "M"ailing address to be the survivor. So if I UNION this, and use the "M"ailing address first, will that be the survivor in the UNION if it also finds a "D"efault address for the same individual? Or vice versa? Or can't I control the survivor?

Thanks for your review and am hopeful for a reply?

PSULionRP

Answer 1

Or can't I control the survivor?

What for a survivor? A UNION select returns distinct values (against a UNION ALL), so it doesn't matter who "survives", it's always the same value you get.

---

Olaf Helper

Blog Xing

Answer 2

declare @t table (sno int,Mcoun varchar(20),Addtype varchar(20))
insert into @t 
values (1,'USA','Mail'),
(1,'AUS','default'),
(2,'USA','Mail'),
(2,'USA','default'),
(3,'USA','Mail'),
(4,'AUS','default')

select sno ,Mcoun from @t where Addtype= 'Mail'
union
Select sno , Mcoun from @t  A
where Addtype = 'Default'  and not exists
(select sno  from @t B where Addtype= 'Mail' and a.Sno=B.Sno )

gets the mailing address instead of default address, if the individual has both address, irrespectivley of whether they are same or not.

If they are same all the time, union would work. As Olaf said, you would not need to care which but I think your requiremtn more like below

if they are or can be different and you want to get mailing address in such case, and only default address, if they do not have mailing address, you can try the code above.

---

Hope it Helps!!

Answer 3

If you give us table definitions and sample data, we could probably do a better job of helping you.  My guess is you don't want to do a union at all, but instead you would want something like

Declare @Customers Table(CustomerID int);
Declare @Addresses Table(CustomerID int, MailingAddressType char(1), Address varchar(30));
Insert @Customers(CustomerID) Values (1), (2), (3);
Insert @Addresses(CustomerID, MailingAddressType, Address) Values
(1, 'D', 'New York'),
(2, 'M', 'Chicago'),
(3, 'D', 'Kansas City'),
(3, 'M', 'Atlanta');

;With cte As 
(Select c.CustomerID, a.Address, Row_Number() Over(Partition By c.CustomerID Order By a.MailingAddressType Desc) As rn
From @Customers c
Inner Join @Addresses a On c.CustomerID = a.CustomerID)
Select CustomerID, Address
From cte
Where rn = 1
Order By CustomerID;

Tom