[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 222
  • Last Modified:

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?
0
rutledgj
Asked:
rutledgj
1 Solution
 
Ephraim WangoyaCommented:

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

0
 
rutledgjAuthor Commented:
This seems to work but puts null in the ssnum col for all records returned.
0
 
TempDBACommented:
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.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
rutledgjAuthor Commented:
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
0
 
CrashmanCommented:
Select * from (
SELECT ptid, f_name, m_name, l_name, [ssnum],
 count(ssnum) over (partition by ssnum) counter      
  FROM [MedscribeSQL].[dbo].[Master]
  where org = 'VKSPA') der where der.Counter > 1
0
 
TempDBACommented:
So, does it works and satisfy your criteria?
0
 
rutledgjAuthor Commented:
So, does it works and satisfy your criteria?

No. Like I said, it returns all nulls for ssnum
0
 
TempDBACommented:
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.
0
 
rutledgjAuthor Commented:
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).
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now