Link to home
Create AccountLog in
Avatar of JessyRobinson1234
JessyRobinson1234

asked on

Distinct in union

Hi,

I have the following union query:

SELECT
      'BECKWITH' as 'SLC',
      SOI,
      Cast(RTrim(substring(supplier_description,patindex('%-%',supplier_description)+1,50)) as nvarchar(50)) 'Supplier'
FROM         CEVA5_Compass.dbo.titems_inv with(nolock)
                   JOIN CEVA5_Compass.dbo.tsupplier on CEVA5_Compass.dbo.titems_inv.supplier_key = CEVA5_Compass.dbo.tsupplier.supplier_key
GROUP BY SOI, supplier_description

UNION ALL

SELECT
      'BECKWITH' as 'SLC',
      SOI,
      Cast(RTrim(substring(supplier_description,patindex('%-%',supplier_description)+1,50)) as nvarchar(50)) 'Supplier'
FROM         NFC_Compass.dbo.titems_inv with(nolock)
                   JOIN NFC_Compass.dbo.tsupplier on NFC_Compass.dbo.titems_inv.supplier_key = NFC_Compass.dbo.tsupplier.supplier_key
GROUP BY SOI, supplier_description

Which yields (subset)

BECKWITH      69640       AVOCENT
BECKWITH      13560       BROCADE
BECKWITH      70020       CERQA
BECKWITH      56730       CHICONY AMERICA, INC
BECKWITH      56730       CHICONY AMERICA, INC
BECKWITH      75160       EATON CORPORATION
BECKWITH      69070       ELPIDA
BECKWITH      GIG01       EMULEX
BECKWITH      72743       FORCECON
BECKWITH      72744       FORCECON


My question is how do I only get distinct values meaning that it is ok to display FORCECON twice since the second column is different (72743 abd 72744) but I only want to display  CHICONY AMERICA, INC once since both rows are identical.

Thank you !
ASKER CERTIFIED SOLUTION
Avatar of Muhammad Ahmad Imran
Muhammad Ahmad Imran
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Kent Olsen
Hi Jessy,

Leo's code is correct.  The explanation is that UNION ALL does just that.  It performs a UNION of ALL rows in the result set.  The UNION operator filters out the duplicates.


Good Luck,
Kent
Avatar of JessyRobinson1234
JessyRobinson1234

ASKER

Thank you both, should I go with suggestion 1?
I would.  :)