Duplicate records in my table

Amudhan Kuppan

8y1.3kSQL

how to find a Duplicate records in my table

anyone please help me

iam tried iam send query please check it this is right or wrong

give me duplicate records from the following tables

Select * from DimMember - firstname,lastname,dob

my query is

select firstname,lastname,dob from dimmember

group by firstname,lastname,dob

this is right if wrong please send me query

8 Replies

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

Shahriar Nikkhah8y ago
Accepted answer
Yes you are getting the duplicated records by USERID in my script
check the PARTITION section it says "partition by UserID" it groups by UserID then it counts using "DENSE_RANK()" function and finally i filter out the records that are used more than once by using the WHERE clause, "SELECT * FROM numbered WHERE rowno > 1 "

You have to learn the scripts line by line
Amudhan Kuppan8y ago
thank you shahriar
Amudhan Kuppan8y ago
hi Shahriar
iam understand little bit but i have one doubt
you display duplicate record ok
but which column is based on duplicate record
siteid,userid,or edate
please tell me
Amudhan Kuppan8y ago
I Want only duplicate records rafnas
Shahriar Nikkhah8y ago
Run the script
check it undersatnd it

then comment line 29 and uncomment line 30

check run the code and understand the ASC section of the PARTITIONS

good luck
CREATE table tblUser
(
        UserID  int
    ,   SiteID  int
    ,   EDate   SmallDateTime
)

GO
;

INSERT INTO tblUser VALUES( 1,1, '2009-01-01')
INSERT INTO tblUser VALUES( 1,2, '2009-02-01')
INSERT INTO tblUser VALUES( 1,3, '2009-03-01')
INSERT INTO tblUser VALUES( 2,1, '2009-01-01')
INSERT INTO tblUser VALUES( 2,2, '2009-02-01')
INSERT INTO tblUser VALUES( 3,2, '2009-02-01')
INSERT INTO tblUser VALUES( 4,2, '2009-02-01')
INSERT INTO tblUser VALUES( 4,5, '2009-05-01')
INSERT INTO tblUser VALUES( 4,6, '2009-06-01')
SELECT *  FROM tblUser ORDER BY UserID , EDate
GO
;

;with numbered as (SELECT rowno = DENSE_RANK() over (partition by UserID order by EDate ASC)
                        ,   UserID ,    SiteID ,    EDate
                        --, COUNT(DISTINCT ID )
                     FROM tblUser
                     )
                    SELECT * FROM numbered WHERE rowno > 1
                    --DELETE numbered WHERE rowno > 1
GO
;

SELECT * FROM  tblUser
GO
;

DROP TABLE tblUser
Rafnas T P8y ago
hi,
can you tell me do you want only duplicate records or you wanted to get records without duplicate
Vignesh Mani8y ago
https://www.mssqltips.com/sqlservertip/4486/find-and-remove-duplicate-rows-from-a-sql-server-table/
Vignesh Mani8y ago
https://www.codeproject.com/Tips/268111/Find-duplicate-values-in-SQL-Server