Duplicate records in my table

Question

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

Shahriar Nikkhah · 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 Kuppan · Answer

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

Shahriar Nikkhah · Answer

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 P · Answer

hi, can you tell me do you want only duplicate records or you wanted to get records without duplicate

Vignesh Mani · Answer

https://www.mssqltips.com/sqlservertip/4486/find-and-remove-duplicate-rows-from-a-sql-server-table/

Amudhan Kuppan · Answer

thank you shahriar

Amudhan Kuppan · Answer

I Want only duplicate records rafnas

Vignesh Mani · Answer

https://www.codeproject.com/Tips/268111/Find-duplicate-values-in-SQL-Server