I have a value like below dataset. Now how I can find the duplicate DataSetID like: 201 & 401 is duplicate record.
Add a comment |
4 Answers
Use PIVOT and ROW_Number
For Non Duplicates
SELECT * FROm Tbl WHERE DateSetID IN ( SELECT DateSetID FROM ( SELECT DateSetID,[Name], [Age], [Gender],ROW_NUMBER() OVER (PARTITION BY [Name], [Age], [Gender] ORDER BY DateSetID) RN FROM (SELECT * FROM Tbl) AS SourceTable PIVOT(MAX(ColumnB) FOR ColumnA IN ([Name], [Age], [Gender]) ) AS PivotTable)Tmp WHERE RN = 1 ); For Duplicates alone
SELECT T.* FROM Tbl T JOIN ( SELECT DatasetID, ColumnA, ColumnB FROM ( SELECT DatasetID, [Name], [Age], [Gender], ROW_NUMBER() OVER (PARTITION BY [Name], [Age], [Gender] ORDER BY DatasetID) RN FROM (SELECT * FROM Tbl) AS SourceTable PIVOT(MAX(ColumnB) FOR ColumnA IN ([Name], [Age], [Gender])) AS PivotTable )Tmp UNPIVOT ( ColumnB FOR ColumnA in ([Name], [Age], [Gender]) ) AS UnpivotOp WHERE RN > 1 )X ON T.ColumnA = X.ColumnA AND T.ColumnB = X.ColumnB; 
2 Comments
Never Die
@RedDevil Sorry, I though of removing duplicates. I have updated now.
riad
Thanks for your ans. it's working fine. But, i need another query .like: if all the column's record is same then return the DataSetID.mean, if the value of Name, Age & Gender of all column is same then return DataSetID. In current Query, if any one value is equal to other then returen the DatasetID. If you change the value of AGE to 36 (which uniqueID =5) then all Dataset Value Shows 201,301,401 . But i need the DataSetID (201 , 401) if all the value of Name,Age & Gender is same.Thanks is advance.
You need to count pivoted rows using ubounded rows window (default)
SELECT * FROm Tbl WHERE DatasetID IN ( SELECT DatasetID FROM ( SELECT DatasetID, [Name], [Age], [Gender] ,count(*) OVER (PARTITION BY [Name], [Age], [Gender]) cnt FROM Tbl PIVOT(MAX(ColumnB) FOR ColumnA IN ([Name], [Age], [Gender]) ) AS PivotTable )Tmp WHERE cnt > 1 ); 
Comments
concat the columns, and apply group by.
select distinct Datesetid from tableA where concat(columnA, columnB) in ( select concat(columnA, columnB) from tableA group by concat(columnA, columnB) having count(1) > 1) see dbfiddle.
5 Comments
riad
sorry, may be i don't get you. where i can use group by?i run the query and getting the error like: Column 'tableA' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Ed Bangga
@riad, group by will group the
count() per same concat(DateSetID, ColumnA, ColumnB)riad
@metal, could you please update the query. Still don't get you and facing the error.
riad
@metal: Column 'tableA.DateSetID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Ed Bangga
@riad, can you try dbfiddle i provided.
write give below query & find duplicate value in table
SELECT DISTINCT FirstName, LastName, MobileNo FROM CUSTOMER;
