All rows from Table in Database B and C, only unique rows from Table in Database A

Question

I've got a requirement to select data from a table which exists in several databases which hinges on excluding duplicates in a specific way. The data is such that rows in databases B and C might contain the same Account Segment (SGMNTID) as rows in database A. When that is true we want to select the row from the B or C database and exclude the row from the A database.

So I believe this is essentially a selection of everything from B and C and a selection of only unique rows from A when compared to B and C.

A.DBO.GL40200 
-----
SGMNTID DSCRIPTN
10      ABN Tech
20      ABN Financial
40      Quo USA
41      Quo AUS
62      PO Shanghai

B.DBO.GL40200
-----
SGMNTID DSCRIPTN
40      Quo USA
41      Quo Pt Ltd
60      PO Singapore

C.DBO.GL40200
-----
SGMNTID DSCRIPTN
62      PO Shanghai
63      PO Purchasing

Desired result set would be:

10      ABN Tech
20      ABN Financial
40      Quo USA
41      Quo Pt Ltd
60      PO Singapore
62      PO Shanghai
63      PO Purchasing

Answer 1

Start with a query like:

with q as
(

    select 'A' company, * 
    from A.DBO.GL40200
    UNION ALL
    select 'B' company, * 
    from B.DBO.GL40200
    UNION ALL
    select 'C' company, * 
    from C.DBO.GL40200

), q2 as
(
  select *, row_number() over (partition by SGMNTID, order by company desc) rn
  from q
)
select SGMNTID, DSCRIPTN
from q2 
where rn = 1