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
Loading
David SmithPosted Jul 31, 2012, 12:56 AM
David SmithPosted Jul 31, 2012, 12:56 AM
Santhosh Kumar JayaramanPosted Jul 31, 2012, 12:51 AM
David SmithPosted Jul 30, 2012, 1:37 PM
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 JayaramanPosted Jul 30, 2012, 10:32 AM
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 SmithPosted Jul 30, 2012, 9:29 AM
Can you explain What is CTE_TEST?
Can you explain "ROwNnumber() over" ?
Santhosh Kumar JayaramanPosted Jul 30, 2012, 4:34 AM
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