SELECT DISTINCT with multiple columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

SELECT DISTINCT with multiple columns

Author

Topic

jarrette
Starting Member

4 Posts

Posted - 2008-10-02 : 10:46:35

Hey all,

Have a quick question concerning a DISTINCT SELECT from 2 columns.

I have a doctor table that has, among others, 2 columns called specialty and specialty2. I need all distinct specialties from both columns.

this doesn't work because it gives me all distinct combinations...

SELECT DISTINCT Specialty, Specialty2
FROM Pharma.Doctors d
ORDER BY Specialty

.Net Developer

jarrette
Starting Member

4 Posts

Posted - 2008-10-02 : 11:01:54

NM, figured it out. Here is the solution for reference...

ALTER PROCEDURE [Pharma].[GetSpecialties]

@CompanyID int

AS

SELECT DISTINCT s.specs
FROM
(
SELECT Specialty AS specs FROM Pharma.Doctors
WHERE CompanyID = @CompanyID OR CompanyID is null
UNION ALL
SELECT Specialty2 AS specs FROM Pharma.Doctors
WHERE CompanyID = @CompanyID OR CompanyID is null
)s
ORDER BY s.specs

.Net Developer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources