DISTINCT - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

DISTINCT

Author

Topic

cnaypi
Starting Member

22 Posts

Posted - 2008-06-03 : 18:11:39

I'm trying to find the Distinct for CODEID but it's still returning duplicate data. What is the best way to write this query so that the result I get are Distinct Codeid?

SELECT DISTINCT dbo.AgreementDurationCode.CodeID, dbo.Item.PartNumber, dbo.ItemShadow.AgreementDurationCode, dbo.AgreementDurationCode.CodeCategory,
dbo.AgreementDurationCode.CodeCategoryID, dbo.AgreementDurationCode.Code, dbo.AgreementDurationCode.CodeName,
dbo.AgreementDurationCode.CodeAbbreviation, dbo.AgreementDurationCode.CodeShortAbbreviation, dbo.Item.ItemName,
dbo.AgreementDurationCode.CodeMaxcimAbbreviation, dbo.ItemShadow.ItemStatusCode, dbo.ItemShadow.LicenseTypeCode
FROM dbo.Item INNER JOIN
dbo.ItemShadow ON dbo.Item.ItemID = dbo.ItemShadow.ItemID INNER JOIN
dbo.AgreementDurationCode ON dbo.Item.AgreementDurationCodeID = dbo.AgreementDurationCode.CodeID
WHERE (dbo.ItemShadow.LicenseTypeCode IN ('SEL', 'OLP', 'OLV')) AND (dbo.ItemShadow.ItemStatusCode = 'COM')

cnaypi
Starting Member

22 Posts

Posted - 2008-06-03 : 18:48:32

I tried the ROW_Number but it didn't work because I found out that the sql server I'm doing this query on is sql 2000. Admin please delete as I will repost in the right forum

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-06-03 : 21:16:02

SELECT DISTINCT dbo.AgreementDurationCode.CodeID
From dbo.Item INNER JOIN
dbo.ItemShadow ON dbo.Item.ItemID = dbo.ItemShadow.ItemID INNER JOIN
dbo.AgreementDurationCode ON dbo.Item.AgreementDurationCodeID = dbo.AgreementDurationCode.CodeID
WHERE (dbo.ItemShadow.LicenseTypeCode IN ('SEL', 'OLP', 'OLV')) AND (dbo.ItemShadow.ItemStatusCode = 'COM')

will return the distinct codeID's, The reason it returns duplicats when you include all the other columns, is due to the rows are not distinct, even though their codeid's may be. Please explain more what you are looking for if this is not it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-04 : 00:03:01

Its better if you can illustrate us what you want with help of some sample data

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources