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 !
JessyRobinson1234Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Muhammad Ahmad ImranDatabase DeveloperCommented:
1.
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

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

2.
select distinct * from
(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))
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kent OlsenDBACommented:
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
0
JessyRobinson1234Author Commented:
Thank you both, should I go with suggestion 1?
0
Kent OlsenDBACommented:
I would.  :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.