asked on

Finding duplicate in Tsql

I'm trying to bring back not only duplicates but also additional information.

I know the ssn is duplicated. I need to also bring back the rowid, and names but when I include those it pairs down the list. The names may or may not match (some last names have Xxx in front of the duplicate)

Using this code I get 49 rows returned with the dup count

SELECT [ssnum], count(ssnum)
FROM [MedscribeSQL].[dbo].[Master]
where org = 'VKSPA'
group by ssnum
having count(ssnum) > 1

I want to also get the ptId (unique table key), f_name, m_name, And l_name.
Can someone show me how to do this without reducing the results?

Ephraim Wangoya

use this

;with cte as
(
  SELECT ptid, f_name, m_name, And l_name. [ssnum], 
        row_number() over (partition by ssnum order by ptid) rn      
  FROM [MedscribeSQL].[dbo].[Master]
  where org = 'VKSPA'
)

select * 
from cte
where rn > 1

Open in new window

rutledgj

ASKER

This seems to work but puts null in the ssnum col for all records returned.

TempDBA

rutledgi,
In the above query ewangoya didn't mean the exact seelct statement. Change his 3rd line with:-

SELECT ptid, f_name, m_name, l_name, [ssnum],

He was trying to tell you the method of doing it.

rutledgj

ASKER

I realize that. My current query looks like this:

;with cte as
(
SELECT ptid, f_name, m_name, l_name, [ssnum],
row_number() over (partition by ssnum order by ptid) rn
FROM [MedscribeSQL].[dbo].[Master]
where org = 'VKSPA'
)

select *
from cte
where rn > 1
order by ssnum, f_name

ASKER CERTIFIED SOLUTION

Haver Ramirez

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

TempDBA

So, does it works and satisfy your criteria?

rutledgj

ASKER

So, does it works and satisfy your criteria?

No. Like I said, it returns all nulls for ssnum

TempDBA

In that case can you check your main table if ssnum is null for those rows which are duplicate.

or just search all the fields with ssnum if null any.

rutledgj

ASKER

Actually crashman's answer worked perfectly. There are no null ssn's in my database. Something wrong with that query (or it doesn't work in sql server 2005).