Duplicate Records in my table

Amudhan Kuppan

8y1kSQL

anyone please help me

how to find a duplicate records in my table

iam tried it will give duplicate record also but my seniors are told this is wrong

wait i will send question and answer any change please update me

give me duplicate records from the following tables..

use Dev_SF_PhysicianPartner

select from dimmember -- firstname,lastname,dob

select from DimPCP --firstname,lastname or npi

select * from DimOfficeLocation -- officename,tin

this is a question

and iam send a one file that is answer please check it any change please update m

10 Replies

Know the answer? Post it — somebody with the same question will find it here.

Shahriar Nikkhah8y ago
Accepted answer
Hi "Divyanshu Tripathi"
Do you think that your solution will work??????
see if your solution works with 3 records from the same name.

Nik
0
Divyanshu Tripathi8y ago
Delete duplicate records
Select * from dup
delete from dup where empid not in ( select min(empid) from dup group by name)
+1
Jayesh Vaghasiya8y ago
Remove Duplicate Records From Table In SQL Server

https://jayeshvaghasiya.blogspot.com/2018/04/remove-duplicate-records-from-employee.html
+1
Amit Kumar8y ago
Hi,

Use below code. I hope your problem will solve. Create a customer table and insert the records in it.
CREATE TABLE CUSTOMER
(
FirstName VARCHAR(50),
LastName  VARCHAR(50),
MobileNo  VARCHAR(15)
);

INSERT INTO CUSTOMER VALUES ('Niraj','Yadav',989898);

INSERT INTO CUSTOMER VALUES ('Chetan','Gadodia',959595);
INSERT INTO CUSTOMER VALUES ('Chetan','Gadodia',959595);

INSERT INTO CUSTOMER VALUES ('Atul','Kokam',42424242);
INSERT INTO CUSTOMER VALUES ('Atul','Kokam',42424242);

INSERT INTO CUSTOMER VALUES ('Vishal','Parte',9394453);
INSERT INTO CUSTOMER VALUES ('Vishal','Parte',9394453);
INSERT INTO CUSTOMER VALUES ('Vishal','Parte',9394453);

INSERT INTO CUSTOMER VALUES ('Jinendra','Jain',121212);
INSERT INTO CUSTOMER VALUES ('Jinendra','Jain',121212);
Select Customer table here
SELECT * FROM CUSTOMER
Now find the duplicate records from cutomer table. Use this query.
SELECT      FirstName, LastName, MobileNo, COUNT(1) as CNT
FROM        CUSTOMER
GROUP BY    FirstName, LastName, MobileNo
HAVING      COUNT(1) > 1
Output:
+2
Shahriar Nikkhah8y ago
ill do one you do the rest
DECLARE @tblDimMember AS TABLE
(   [id]        Int
,   [FirstName]     nVarChar(10)
,   [LastName]      nVarChar(10)
,   [DOB]           Date
)

INSERT INTO @tblDimMember
VALUES (1,'A','A','2018-01-03') , (2,'B','B','2018-02-02') , (5, 'V','V','2018-11-11') , (1,'A','A','2018-01-01') , (2, 'B','B','2018-02-02')

--- For Test
SELECT  MT.* FROM   @tblDimMember MT


DELETE MT
FROM    (   SELECT  [RowNumber] = ROW_NUMBER() OVER (PARTITION BY [FirstName] , [LastName] , [DOB] ORDER BY [id] ASC )
            ,   MT.*
            FROM   @tblDimMember MT
        ) MT
WHERE [RowNumber] > 1

--DELETE MT
--FROM @tblDimMember MT
--INNER JOIN
--  (   SELECT  [id]  , COUNT(*) AS idRowCount
--      FROM    @tblDimMember
--      GROUP BY [id]
--      HAVING COUNT(*) > 1
--  ) ST
--ON MT.[id] = ST.[id]


SELECT  MT.* FROM   @tblDimMember MT ORDER BY id
+2
Shahriar Nikkhah8y ago
sorry i cant open rar
and Manish is right your should learn and google , you have asked the same question over and over again, and required different solutions for a different angle, and we have replyed with code and sample, anyways my point is we will ALWAYS help you for free but we love to see that you learn and help others

anyways sorry i cant open the rar file
+1
Amudhan Kuppan8y ago
this is a google help and he told no it is not duplicate check DOB
its different
+1
Manish Kumar8y ago
They are right .Try your self other wise you will not learn any thing. Take help from google.
+1
Amudhan Kuppan8y ago
already ask he told tried yourself please reply me any change
0
Manish Kumar8y ago
Ask this issue to your senior.
+1