• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 263
  • Last Modified:

SQL for Finding Duplicates

How do I write the code for isolating records where a spcific field in the records may have dupliate values.  I want to keep these records.  It's acutally, two fields, but I figured if I could figure how to write for one, that would be easily expanded to mulitple fields.

I'm using SQL Server.

Hope to hear from someone soon.  Merry Christmas, all.
0
Luis5011
Asked:
Luis5011
  • 2
  • 2
  • 2
  • +3
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
Hello Luis5011,

check for duplicates in col1 alone

select col1
from youtable
group by col1
having count(*) > 1


check for duplicates in col1 and col2

select col1 ,col2
from youtable
group by col1 ,col2
having count(*) > 1




Regards,

Aneesh
0
 
Luis5011Author Commented:
Aneesh, does it have to be count(*)>1 or can I have a specific field give I have other fields in my table?  For example, below.  Shouldn't this have worked?

PROC SQL;
 CREATE TABLE WORK.SNFMEDCLMS AS SELECT
            SMC.PERS_GEN_KEY,
            SMC.SRC_MBR_ID,
            SMC.SRC_PROV_ID,
             SMC.SRC_ADMIT_DATE,
             SMC.DC_DATE,
             SMC.PAID_AMT
FROM WORK.SNF_MED_CLAIMS AS SMC
HAVING COUNT(SMC.SRC_MBR_ID) > 1;
QUIT;
0
 
SharathData EngineerCommented:
Can you let us know in which db, you are working?

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Luis5011Author Commented:
I'm working in SAS.  Our data's feed from Oracle.
0
 
SharathData EngineerCommented:
your query lacks GROUP BY. In case of multiple fields, you can try using HAVING COUNT(*) > 1.
Did you face any issues with that?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Count(*) gives the total count for each group, that means, in case no duplicates  it will be 1, thats why it should be >1 for duplicated records
0
 
Shaju KumbalathCommented:
Try this

CREATE TABLE WORK.SNFMEDCLMS AS
SELECT SMC.PERS_GEN_KEY,
SMC.SRC_MBR_ID,
SMC.SRC_PROV_ID,
SMC.SRC_ADMIT_DATE,
SMC.DC_DATE,
SMC.PAID_AMT
FROM WORK.SNF_MED_CLAIMS AS SMC
where exists (Select 'x' from SHAJU.LAB1 smc1
where smc.SRC_MBR_ID = smc1.SRC_MBR_ID
and A1.ROWID <> A2.ROWID);
0
 
Ravi KallaCommented:
try something like this.

Note: try to fix any syntax errors of your own... if exists

Best Wishes & Merry Christmas
PROC SQL;
 CREATE TABLE WORK.SNFMEDCLMS AS SELECT 
            SMC.PERS_GEN_KEY,
            SMC.SRC_MBR_ID,
            SMC.SRC_PROV_ID,
             SMC.SRC_ADMIT_DATE,
             SMC.DC_DATE,
             SMC.PAID_AMT 
FROM WORK.SNF_MED_CLAIMS AS SMC 
WHERE SMC.SRC_MBR_ID IN (SELECT SMC1.SRC_MBR_ID  FROM WORK.SNF_MED_CLAIMS AS SMC1 GROUP BY SMC1.SRC_MBR_ID HAVING COUNT(*) > 1);
QUIT;

Open in new window

0
 
Shaju KumbalathCommented:
SORRY THERE WAS SOME ERROR IN THE PREVIUOS POST
CREATE TABLE WORK.SNFMEDCLMS AS
SELECT SMC.PERS_GEN_KEY,
SMC.SRC_MBR_ID,
SMC.SRC_PROV_ID,
SMC.SRC_ADMIT_DATE,
SMC.DC_DATE,
SMC.PAID_AMT
FROM WORK.SNF_MED_CLAIMS AS SMC
where exists (Select 'x' from WORK.SNF_MED_CLAIMS smc1
where smc.SRC_MBR_ID = smc1.SRC_MBR_ID
and A1.ROWID <> A2.ROWID);
0
 
awking00Commented:
As often is the case, it would be most helpful if you could provide some sample data and the expected output.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now