Selecting Unique Records out of Duplicates

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)

Selecting Unique Records out of Duplicates

Author

Topic

rachelb
Starting Member

3 Posts

Posted - 2012-09-12 : 14:48:16

Hello,

I was wondering if someone can help me with this query. I am having issues with having duplicate a2.Id (ParentSFID) for a.Id (ChildSFID). Is there way that I can limit this so it finds only one of the ParentSFID for the ChildSFID.

Thanks!

Here's my query:

SELECT
		    a.SPID__c AS ChildSPID ,
                    a.Id AS ChildSFID ,
                    a.Name AS ChildName ,
                    a.Type AS ChildType ,
                    a2.SPID__c AS ParentSPIDDatabase ,
                    a2.Id AS ParentSFID ,
                    a2.Name AS ParentName ,
                    a2.Type AS ParentType
            FROM    angie.dbo.SPRelationship AS sr WITH ( NOLOCK )
            INNER JOIN dbo.Account AS a WITH ( NOLOCK ) ON sr.ToSPID = a.SPID__c
            INNER JOIN dbo.Account AS a2 WITH ( NOLOCK ) ON sr.FromSPID = a2.SPID__c
            WHERE   a.Provider_Type__c = 'physician'
                    AND a.Type <> 'deleted'
                    AND a2.Type <> 'deleted'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-12 : 14:52:01

[code]
SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY a.SPID__c ORDER BY a2.Id) AS Seq,
a.SPID__c AS ChildSPID ,
a.Id AS ChildSFID ,
a.Name AS ChildName ,
a.Type AS ChildType ,
a2.SPID__c AS ParentSPIDDatabase ,
a2.Id AS ParentSFID ,
a2.Name AS ParentName ,
a2.Type AS ParentType
FROM angie.dbo.SPRelationship AS sr WITH ( NOLOCK )
INNER JOIN dbo.Account AS a WITH ( NOLOCK ) ON sr.ToSPID = a.SPID__c
INNER JOIN dbo.Account AS a2 WITH ( NOLOCK ) ON sr.FromSPID = a2.SPID__c
WHERE a.Provider_Type__c = 'physician'
AND a.Type <> 'deleted'
AND a2.Type <> 'deleted'
)t
WHERE Seq=1
[/code]

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

rachelb
Starting Member

3 Posts

Posted - 2012-09-13 : 08:15:35

That works perfectly! Thanks a lot!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-13 : 10:21:30

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources