Duplicate Name Different Numbers

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

General SQL Server Forums

New to SQL Server Programming

Duplicate Name Different Numbers

Author

Topic

Les.61
Starting Member

49 Posts

Posted - 2010-02-25 : 00:18:42

I have 3 columns, Surname, GivenName and ClientID.

I want to locate all clients that have the same name but different number ie

Surname GivenName ClientID
SMITH JOHN 12345
SMITH JOHN 12345
SMITH JOHN 25845

I would like to be able to report on the lines if the names match but numbers do not but only one of the duplicated lines ie

Surname GivenName ClientID
SMITH JOHN 12345
SMITH JOHN 25845

I can highlight duplicates but not the 'odd' one out. So far I have

use livereporting
go
select surname, givenname
from dbo.FinancialClaims2
group by surname, givenname, clientid
having count (*) > 1
Can anyone point me in the right direction?

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-02-25 : 00:21:28

select surname, givenname, clientid
from dbo.FinancialClaims2
group by surname, givenname, clientid

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-25 : 00:27:24

[code]
select surname, givenname
from dbo.FinancialClaims2
group by surname, givenname, ~~clientid~~
having count (*) > 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-25 : 09:13:18

[code]
SELECT surname, givenname,ClientID
FROM
(
SELECT surname, givenname,ClientID,
COUNT(ClientID) OVER (PARTITION BY surname, givenname) AS Cnt
FROM
(
SELECT surname, givenname,ClientID,
ROW_NUMBER() OVER (PARTITION BY surname, givenname,ClientID ORDER BY ClientID) AS Seq
FROM Table
)t
WHERE Seq=1
)r
WHERE Cnt > 1
[/code]

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

Les.61
Starting Member

49 Posts

Posted - 2010-02-25 : 19:05:24

visakh16,
Thanks for the script it worked a treat. The other 2 scripts gave duplicates on all 3 columns and not those that duplicated the first 2 but had different 3rd column.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-26 : 08:13:03

welcome

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

Sachin.Nand

2937 Posts

Posted - 2010-02-26 : 08:26:21

Try this one to


select * from 
(
select *,RANK()over(order by ClientId,Surname GivenName )-Row_Number()over(order by Surname GivenName )as rowid from yourtable

)t where rowid=0

PBUH

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-03-01 : 03:49:49

[code]--My Simple Query
SELECT DISTINCT Surname, GivenName, ClientID
FROM table_name AS T1
WHERE EXISTS
(SELECT *
FROM table_name AS T2
WHERE T2.Surname = T1.Surname
AND T2.GivenName = T1.GivenName
AND T2.ClientID <> T1.ClientID);
[/code]

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-03-01 : 03:53:50

quote:

Originally posted by Idera

Try this one to


select * from 
(
select *,RANK()over(order by ClientId,Surname GivenName )-Row_Number()over(order by Surname GivenName )as rowid from yourtable

)t where rowid=0

PBUH

Sorry, your query is not equivalence visakh16 solution. see my method.

Sachin.Nand

2937 Posts

Posted - 2010-03-01 : 04:43:32

quote:
Originally posted by ms65g

quote:
Originally posted by Idera

Try this one to

select * from
(
select *,RANK()over(order by ClientId,Surname GivenName )-Row_Number()over(order by Surname GivenName )as rowid from yourtable

)t where rowid=0

PBUH

Sorry, your query is not equivalence visakh16 solution. see my method.

Can you give me a reason why my solution wont work?

PBUH

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-03-01 : 05:12:57

quote:
Originally posted by Idera

quote:
Originally posted by ms65g

quote:
Originally posted by Idera

Try this one to

select * from
(
select *,RANK()over(order by ClientId,Surname GivenName )-Row_Number()over(order by Surname GivenName )as rowid from yourtable

)t where rowid=0

PBUH

Sorry, your query is not equivalence visakh16 solution. see my method.

Can you give me a reason why my solution wont work?

PBUH

Your query work! but it no equivalence with other correct queries.
just compare the result sets.

DECLARE @t TABLE (Surname varchar(15), GivenName varchar(15), ClientID varchar(15))
INSERT INTO @t
SELECT 'SMITH', 'JOHN', '12345' UNION ALL
SELECT 'SMITH', 'JOHN', '12345' UNION ALL
SELECT 'SMITH', 'JOHN', '25845' UNION ALL
SELECT 'Test1','Test1','Test1' UNION ALL
SELECT 'Test2','Test2','Test2'

--Visakh16 Query
SELECT surname, givenname,ClientID
FROM
(
SELECT surname, givenname,ClientID,
COUNT(ClientID) OVER (PARTITION BY surname, givenname) AS Cnt
FROM
(
SELECT surname, givenname,ClientID,
ROW_NUMBER() OVER (PARTITION BY surname, givenname,ClientID ORDER BY ClientID) AS Seq
FROM @t
)t
WHERE Seq=1
)r
WHERE Cnt > 1
/*
surname         givenname       ClientID
--------------- --------------- ---------------
SMITH           JOHN            12345
SMITH           JOHN            25845
*/--My Simple Query
SELECT DISTINCT Surname, GivenName, ClientID
  FROM @t AS T1
 WHERE EXISTS 
       (SELECT *
         FROM @t AS T2
        WHERE T2.Surname = T1.Surname
          AND T2.GivenName = T1.GivenName
          AND T2.ClientID <> T1.ClientID);
/*Surname         GivenName       ClientID
--------------- --------------- ---------------
SMITH           JOHN            12345
SMITH           JOHN            25845
*/--Your Query
select * from 
(
select *,RANK()over(order by ClientId,Surname ,GivenName )-Row_Number()over(order by Surname, GivenName )as rowid from @t

)t where rowid=0
/*Surname         GivenName       ClientID        rowid
--------------- --------------- --------------- --------------------
SMITH           JOHN            12345           0
SMITH           JOHN            25845           0
Test1           Test1           Test1           0
Test2           Test2           Test2           0
*/

Sachin.Nand

2937 Posts

Posted - 2010-03-01 : 08:27:22

So in this case neither urs nor visakhs query is right because it is not getting any output for Test1 & Test2 while the requirement of the OP is

if the names match but numbers do not but only one of the duplicated lines

Since Test1 & Test2 do not have a duplicate so ideally the query shud return them right???

Ideally I would want it that way & I beleive OP to wants that way.

PBUH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-01 : 10:35:12

quote:
Originally posted by Idera

So in this case neither urs nor visakhs query is right because it is not getting any output for Test1 & Test2 while the requirement of the OP is

if the names match but numbers do not but only one of the duplicated lines

Since Test1 & Test2 do not have a duplicate so ideally the query shud return them right???

Ideally I would want it that way & I beleive OP to wants that way.

PBUH

Nope OP sounded like (s)he's interested only to report on the lines if the names match but numbers do not but only one of the duplicated lines

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources