Skip to content
Advertisement

Remove duplicate based on 2 rows value

So I have a database with more than 2000 line and I wanted to delete the duplicate value based on 2 rows.

For Example :

no     server     name
1      serv1       a
2      serv1       b
3      serv1       b
4      serv1       b
5      serv2       a
6      serv2       b
7      serv2       c
8      serv2       c

So basically I wanted to remove the duplicate IF two of the rows have a duplicate
BUT I dont want to remove them if just one of the row has duplicate.

Expected Output:

no      server     name
1       serv1       a
2       serv1       b
3       serv2       a
4       serv2       b
5       serv2       c

Any answer would be appreciated.

Advertisement

Answer

There is many ways to do what you want. If you ‘re looking for just SELECTing the data without duplicates then you could use:

  • DISTINCT

    SELECT DISTINCT Server,
                    Name
    FROM YourTableName
    
    
  • GROUP BY

    SELECT Server,
           Name
    FROM YourTableName
    GROUP BY Server, Name
    
    
  • Window function (ROW_NUMBER) in a subquery

    SELECT Server, 
           Name
    FROM
    (
      SELECT *,
             ROW_NUMBER() OVER(PARTITION BY Server, Name ORDER BY Server) RN
      FROM YourTableName
    ) TT
    WHERE TT.RN = 1
    
    

You delete the duplicates as

DELETE T1
FROM
(
  SELECT ROW_NUMBER() OVER(PARTITION BY Server, Name ORDER BY Server) RN
  FROM T --YourTableName
) T1 JOIN
(
  SELECT ROW_NUMBER() OVER(PARTITION BY Server, Name ORDER BY Server) RN
  FROM T --YourTableName
) T2 ON T1.RN = T2.RN
WHERE T1.RN > 1;

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement