CODEWITHSUNDEEP
sqlsql-server
riad
riad

Reputation: 7184

SQL Server : find duplicate record by column value

I have a value like below dataset. Now how I can find the duplicate DataSetID like: 201 & 401 is duplicate record.

enter image description here

Upvotes: 0

Views: 112

Answers (4)

Never Die
Never Die

Reputation: 331

Use PIVOT and ROW_Number

For Non Duplicates

FIDDLE DEMO

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

FIDDLE DEMO

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;

Upvotes: 3

Serg
Serg

Reputation: 22811

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
);

Fiddle

Upvotes: 1

shivangi yadav
shivangi yadav

Reputation: 11

write give below query & find duplicate value in table

SELECT DISTINCT FirstName, LastName, MobileNo FROM CUSTOMER;

Upvotes: 0

Ed Bangga
Ed Bangga

Reputation: 13006

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.

Upvotes: 0

Related Questions