Trouble Using DISTINCT function

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

General SQL Server Forums

New to SQL Server Programming

Trouble Using DISTINCT function

Author

Topic

evanburen
Posting Yak Master

167 Posts

Posted - 2008-02-27 : 11:19:58

I need to return a single, unique record for each director. In my example here, I'm getting back 5 records for Bossidy because he has 5 records in the Directorships table each with a unique CompID.

SELECT TDirectors.IDDir, TDirectors.DirFName, TDirectors.DirLName, TDirectors.DirLName + ', ' + TDirectors.DirFName AS DirFullName, TDirectors.ExecutiveTitle, TDirectors.DirGender, TDirectors.DirAge, TDirRace.DirRace,
TDirectors.PrincipalCompany AS CompanyName, TDirectorships.CompID
FROM TDirRace RIGHT OUTER JOIN
TDirectors ON TDirRace.IDDir = TDirectors.IDDir RIGHT OUTER JOIN
TDirectorships ON TDirectors.IDDir = TDirectorships.IDDir
WHERE (TDirectors.DirLName='Bossidy')

I thought I could do this but it doesn't work.

SELECT DISTINCT(TDirectors.IDDir)...

Thanks

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-02-27 : 11:22:02

quote:
Originally posted by evanburen

I need to return a single, unique record for each director. In my example here, I'm getting back 5 records for Bossidy because he has 5 records in the Directorships table each with a unique CompID.

SELECT TDirectors.IDDir, TDirectors.DirFName, TDirectors.DirLName, TDirectors.DirLName + ', ' + TDirectors.DirFName AS DirFullName, TDirectors.ExecutiveTitle, TDirectors.DirGender, TDirectors.DirAge, TDirRace.DirRace,
TDirectors.PrincipalCompany AS CompanyName, TDirectorships.CompID
FROM TDirRace RIGHT OUTER JOIN
TDirectors ON TDirRace.IDDir = TDirectors.IDDir RIGHT OUTER JOIN
TDirectorships ON TDirectors.IDDir = TDirectorships.IDDir
WHERE (TDirectors.DirLName='Bossidy')

I thought I could do this but it doesn't work.

SELECT DISTINCT(TDirectors.IDDir)...

Thanks

When there are duplicate records, which record do you want returned?

evanburen
Posting Yak Master

167 Posts

Posted - 2008-02-27 : 11:28:18

Just the first one. It doesn't matter, really, as long as just instance of the person's name appears. Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-27 : 11:34:28

Try this:-

SELECT TDirectors.IDDir, TDirectors.DirFName, TDirectors.DirLName, TDirectors.DirLName + ', ' + TDirectors.DirFName AS DirFullName, TDirectors.ExecutiveTitle, TDirectors.DirGender, TDirectors.DirAge, TDirRace.DirRace, 
TDirectors.PrincipalCompany AS CompanyName, tmp.MaxComp
FROM TDirRace RIGHT OUTER JOIN
TDirectors ON TDirRace.IDDir = TDirectors.IDDir  RIGHT OUTER JOIN
(SELECT IDDir,MAX(CompID) AS MaxComp FROM TDirectorships GROUP BY IDDir) tmp
ON tmp.IDDir=TDirectors.IDDir
WHERE (TDirectors.DirLName='Bossidy')

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-02-27 : 11:35:58

This will give the last record added for a last name:

SELECT  TDirectors.IDDir,
        TDirectors.DirFName,
        TDirectors.DirLName,
        TDirectors.DirLName + ', ' + TDirectors.DirFName AS DirFullName,
        TDirectors.ExecutiveTitle,
        TDirectors.DirGender,
        TDirectors.DirAge,
        TDirRace.DirRace,
        TDirectors.PrincipalCompany AS CompanyName,
        TDirectorships.CompID
FROM    TDirRace
        RIGHT OUTER JOIN TDirectors ON TDirRace.IDDir = TDirectors.IDDir
        RIGHT OUTER JOIN TDirectorships ON TDirectors.IDDir = TDirectorships.IDDir
WHERE   TDirectors.IDDir = ( SELECT MAX(IDDir)
                             FROM   TDirectors
                             WHERE  ( DirLName = 'Bossidy' )
                           )

evanburen
Posting Yak Master

167 Posts

Posted - 2008-02-27 : 12:46:32

Great. Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources