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 1234. 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