Remove duplicate based on 2 rows value

Question

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.

Do you want to **delete** the rows in the table or just not show them in the result of a SELECT query? — Paul Spiegel, Sep 07 '19 at 17:34
Do you only have these two columns? Is there a primary key defined? What is your MySQL version? — Paul Spiegel, Sep 08 '19 at 11:54
I've edited the question. I have a ```no``` table which is a primary key, and my sql version is ```5.7.21``` @Paul Spiegel — Ron, Sep 08 '19 at 14:11
Then use the answer of Rakesh Jakhar and replace `id` with `no`. — Paul Spiegel, Sep 08 '19 at 14:13
You can also try [this](https://stackoverflow.com/questions/36647058/removing-duplicates-with-unique-index) — Paul Spiegel, Sep 08 '19 at 14:19

Gordon Linoff · Answer 1 · 2019-09-08T17:06:22.780

1

Use select distinct:

select distinct server, name
from t;

If you have a lot of duplicates, the simplest way is probably to recreate the table:

create table temp_t as
    select min(no) as no, server, name
    from t
    group by server, name;

truncate table t;

insert into t (no, server, name)
    select no, server, name
    from temp_t;

If you don't have many rows, then delete is fine:

delete t
    from t join
         (select server, name, min(no) as min_no
          from t
          group by server, name
         ) tt
         on t.server = tt.server and t.name = tt.name
    where t.no > tt.min_no;

edited Sep 08 '19 at 17:06

answered Sep 07 '19 at 17:09

Gordon Linoff

1,242,037
58
646
786

Hey, @Gordon Linoff.do you know a way how to delete the duplicate data from the database too? – Ron Sep 08 '19 at 03:47

Ilyes · Accepted Answer · 2019-09-08T06:16:37.817

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;

can you tell me how to delete the duplicate too? – Ron Sep 08 '19 at 03:45 — Ron, Sep 08 '19 at 03:45
You can't delete from a subquery - at least not in MySQL. – Paul Spiegel Sep 10 '19 at 15:54 — Paul Spiegel, Sep 10 '19 at 15:54

score 0 · Answer 3 · answered Sep 08 '19 at 06:22

0

You can use the following query to remove duplicates

DELETE t1 FROM tablename t1 INNER JOIN tablename t2 
WHERE t1.id < t2.id AND t1.server = t2.server AND t1.name = t2.name;

answered Sep 08 '19 at 06:22

Rakesh Jakhar

6,380
2
11
20

Remove duplicate based on 2 rows value

3 Answers3