|
ppatel112
Starting Member
19 Posts |
 Posted - 07/11/2012 : 01:43:26
|
Hi there,
i have a following query:
SELECT rowno,elink_entityid,ELink_RecordID,ELink_Type,* FROM
(SELECT * ,
ROW_NUMBER() OVER(PARTITION BY emai_emailaddress,Elink_recordid ORDER BY emai_emailaddress,Elink_recordid DESC) as rowno
FROM dbo.email LEFT JOIN emaillink ON elink_emailid = Emai_EmailId
where Emai_emailaddress in (select Emai_emailaddress from email LEFT JOIN emaillink ON elink_emailid = Emai_EmailId
GROUP BY Emai_emailaddress, ELink_RecordID HAVING ((count(Emai_emailaddress)>1) and (count(Elink_recordid)>=1)))
) as Duplicates
that returns below:
rowno,elink_entityid,ELink_RecordID,ELink_Type
1 5 1348 Business 3321
1 13 2051 Business 3306
1 13 2069 Business 3308
1 13 2078 Business 3311
1 13 2121 Business 3320
2 13 2121 Private 3404
1 13 2125 Business 3322
what is just need is elink_recordid 2121 and not the rest.
how can i acheive this?
regards |
|
getting the right data only
