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.

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;