getting the right data only - 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 2008 Forums

Transact-SQL (2008)

getting the right data only

Author

Topic

ppatel112
Starting Member

35 Posts

Posted - 2012-07-11 : 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

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2012-07-11 : 04:25:19

Just place where clause at the end of your query (Whats worng )
WHERE elink_recordid = 2121

--------------------------
http://connectsql.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-07-11 : 05:10:48

looks like the solution is to just delete the equal sign here:
and (count(Elink_recordid)>=1)))

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-11 : 10:09:10

is it all 2121 records irrespective of other conditions or do you've other logic to apply as well?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources