SQL Distinct

Question

Can someone tell me why the dataset below keeps returning duplicate ContactIds and I am asking for Distinct in the query. I am totally confused.

SELECT Distinct tblContact.ContactID, (tblContact.FirstName) AS MinOfFirstName, tblContact.LastName, tblContact.PhoneHome, tblContact.Location, tblContact.AddressStreet, tblContact.AddressStreetCity, tblContact.AddressStreetZIP, tblContact.AddressDirections, tblContact.AddressPost, tblContact.AddressPostCity, tblContact.AddressPostZIP, tblContact.EmailAddress, tblContact.Gender, tblContact.Spouse, tblContact.Title, tblContactType.ContactStatus, tblContact.Birthday, tblContact.GAP
FROM (tblContact LEFT JOIN tblContactType ON tblContact.ContactID = tblContactType.ContactID) LEFT JOIN tblContactService ON tblContact.ContactID = tblContactService.ContactID
Where tblContactService.ServiceType = 2

David Smith · Answer

i cant remember

David Smith · Answer

i cant remember

Santhosh Kumar Jayaraman · Answer

What error you got when using my query?

David Smith · Answer

No sir it did not work, I finally got it work with the query below: SELECT DISTINCT tblContact.ContactID, tblContact.FirstName, tblContact.LastName, tblContact.PhoneHome, tblContact.Location, tblContact.AddressStreet, tblContact.AddressStreetCity, tblContact.AddressStreetZIP, tblContact.AddressDirections, tblContact.AddressPost, tblContact.AddressPostCity, tblContact.AddressPostZIP, tblContact.EmailAddress, tblContact.Gender, tblContact.Spouse, tblContact.Title, tblContact.Birthday, tblContact.GAP, ( Select MAX(CT.ContactStatus) From tblContactType AS CT Where tblContact.ContactID = CT.ContactID Group By CT.ContactID ) AS ContactStatus FROM tblContact INNER JOIN tblContactService ON tblContact.ContactID = tblContactService.ContactID WHERE (tblContactService.ServiceType) =2 ORDER BY tblContact.LastName, tblContact.FirstName;

Santhosh Kumar Jayaraman · Answer

1. With comes along with CTE syntax. I have used Common table expressions (CTE) here. Normal CTE syntax is with [CTENAME] (select * from tbl_temp) Select * from CTENAME. Like this. 2. CTE_Test is just my cte name. 3. Rownumber over , you can get to know about rownumber() in this link. http://msdn.microsoft.com/en-us/library/ms186734.aspx in General, if there are 4 rows., Then each row will have rownumber as 1,2,3,4. The position in which they are present in a set of result. s I have partitioned table on ContactId column. so if there are multiple records for contactid 1, then it will grouped and rownumber will be set in sequence. So whenever i select rownumber=1, it will fetch only one row for each partition. Btw, the query worked??

David Smith · Answer

Can you explain the "With syntax" or what the "With is doing"? Can you explain What is CTE_TEST? Can you explain " ROwNnumber() over" ?

Santhosh Kumar Jayaraman · Answer

This Disctinct is applicable for all columns in the select clasuse, its not only for contactIds, try this. With CTE_TEST as (SELECT tblContact.ContactID, (tblContact.FirstName) AS MinOfFirstName, tblContact.LastName, tblContact.PhoneHome, tblContact.Location, tblContact.AddressStreet, tblContact.AddressStreetCity, tblContact.AddressStreetZIP, tblContact.AddressDirections, tblContact.AddressPost, tblContact.AddressPostCity, tblContact.AddressPostZIP, tblContact.EmailAddress, tblContact.Gender, tblContact.Spouse, tblContact.Title, tblContactType.ContactStatus, tblContact.Birthday, tblContact.GAP, ROwNnumber() over(partition by tblContact.ContactID) as rowno FROM (tblContact LEFT JOIN tblContactType ON tblContact.ContactID = tblContactType.ContactID) LEFT JOIN tblContactService ON tblContact.ContactID = tblContactService.ContactID Where tblContactService.ServiceType = 2 ) Select * from CTE_TEST where rowno=1

SQL Distinct

Answers (7)