Identify “duplicates” within a table by using GROUP BY column1 OR column2

Question

Generally speaking, I want to group by one column OR by another one:

SELECT count(*)
FROM foo
GROUP BY column1 OR column2

In particular, I want to identify duplicates within a given table, below an example with minimal data.

Minimal example

| id | name       | birth_date   | email                        |
|---:|:-----------|:-------------|:-----------------------------|
|  1 | 'Gamow'    | '1904-03-04' | '[email protected]'    |
|  2 | 'Gamow'    | NULL         | NULL                         |
|  3 | 'Gamow'    | '1904-03-04' | NULL                         |
|  4 | 'Gamow'    | '1904-03-04' | '[email protected]'    |
|  5 | 'Gamow'    | NULL         | '[email protected]'    |
|  6 | 'Feynman'  | '1918-05-11' | '[email protected]' |
|  7 | 'Feynman'  | '1918-05-11' | NULL                         |
|  8 | 'Poincaré' | '1854-04-29' | '[email protected]'  |

Two people are considered duplicates if they have the same name AND (the same birth date OR the same email).
I would like to find a query who gives

• MIN(id) among the duplicates: the line to keep
• the 1+ other ids: the line(s) to delete

I wrote this query

SELECT 
    MIN(p.id) AS id_tokeep,
    REPLACE(
        GROUP_CONCAT(p.id ORDER BY p.id ASC SEPARATOR ','),
        CONCAT(MIN(p.id), ','),
        ''
    ) AS ids_todelete,
    MIN(p.name) AS name
FROM people AS p
WHERE p.birth_date IS NOT NULL OR p.email IS NOT NULL 
GROUP BY p.name, (p.birth_date IS NOT NULL) OR (p.email IS NOT NULL)
HAVING COUNT(id) > 1
ORDER BY id_tokeep;

which works:

| id_tokeep | ids_todelete | name      |
|----------:|--------------|-----------|
|         1 | '3,4,5'      | 'Gamow'   |
|         6 | '7'          | 'Feynman' |

except:

• it feels like a hack (as I didn’t find anywhere the use of OR inside a GROUP BY clause),
• it sometimes generate a warning « Truncated incorrect DOUBLE value: '[email protected]' »
• with real data, it returns « strange » results (which seems to confirm that it is a hack).

Hence, my questions are:

• is it « legal » to use OR within a GROUP BY clause?
• if not, how to re-write this query without the OR within the GROUP BY?

SQL code to generate the minimal example

DROP TABLE IF EXISTS people;

CREATE TABLE people (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    birth_date DATE DEFAULT NULL,
    email VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO people (name, birth_date, email)
VALUES
    ('Gamow', '1904-03-04', '[email protected]'),  -- 1
    ('Gamow', NULL, NULL),  -- 2
    ('Gamow', '1904-03-04', NULL),  -- 3
    ('Gamow', '1904-03-04', '[email protected]'),  -- 4
    ('Gamow', NULL, '[email protected]'),  -- 5
    ('Feynman', '1918-05-11', '[email protected]'),  -- 6
    ('Feynman', '1918-05-11', NULL),  -- 7
    ('Poincaré', '1854-04-29', '[email protected]')  -- 8
;

Other trial query

I tried with two self-join, but I then struggle to group the duplicates:

SELECT 
    p1.id AS patient_id,
    p2.id AS patient_match_birth_date_id,
    p3.id AS patient_match_email_id
FROM people AS p1
    JOIN people AS p2 ON p2.birth_date = p1.birth_date
    JOIN people AS p3 ON p3.email = p1.email
ORDER BY p1.id, p2.id, p3.id;

I tried with a union between the duplicate on birth date and the duplicate on email, but it returns retundant data:

WITH patient_matches AS (
    (
        SELECT 
            p1.id AS patient_id,
            p2.id AS patient_match_id
        FROM people AS p1
            JOIN people AS p2 ON p2.birth_date = p1.birth_date
        ORDER BY p1.id ASC 
    )
    UNION ALL 
    (
        SELECT 
            p1.id AS patient_id,
            p2.id AS patient_match_id
        FROM people AS p1
            JOIN people AS p2 ON p2.email = p1.email
        ORDER BY p1.id ASC 
    )
    ORDER BY patient_match_id
)
SELECT json_arrayagg(pm.patient_id)
FROM patient_matches AS pm
GROUP BY pm.patient_match_id;

Answer 1

What I propose ?

Unique rows in your case can be found by using

select min(p.id) id,
       p.name
from people p
inner join (select min(id) id,
                   name
            from people
            group by name,email 
          )  x on x.id=p.id
group by p.name,p.birth_date

Create another table people_new

create table people_new  (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    birth_date DATE DEFAULT NULL,
    email VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB ;

Use insert into select to insert unique rows

insert into people_new ( select * 
                         from people 
                         where id in ( select min(p.id) id
                                       from people p
                                       inner join (select min(id) id
                                                from people
                                                group by name,email 
                                                )  x on x.id=p.id
                                    group by p.name,p.birth_date
                                    )
                        )

Select on people_new would give the following result.

id  name      birth_date    email
 1  Gamow     1904-03-04    [email protected]
 2  Gamow     null          null
 6  Feynman   1918-05-11    [email protected]
 8  Poincaré  1854-04-29    [email protected]

Check fiddle

At the end you can rename the tables and drop the old table if you don't need it anymore.

SET autocommit=0;
LOCK TABLES people WRITE;
ALTER TABLE `people` RENAME people_old;
COMMIT;
UNLOCK TABLES;


SET autocommit=0;
LOCK TABLES people_new WRITE;
ALTER TABLE `people_new` RENAME people;
COMMIT;
UNLOCK TABLES;

Note, delete is a heavy process , better us insert into select and drop the old table.

Answer 2

So I found a solution, there is two points:

1. The OR within a GROUP BY clause is not interpreted in a GROUP BY-context, it is just a simple OR, ie (p.birth_date IS NOT NULL) OR (p.email IS NOT NULL) within GROUP BY p.name, ... is the same as within SELECT ...; it gave the impression of working because in the sample data above, columns birth_date and email was either NULL either equals.
2. Afaik, it is not possible to use a GROUP BY to identify the duplicates, one has to use a self-join as below.

Using self-JOIN with specific ON clause

This query:

WITH duplicates AS (
    SELECT 
        p1.id AS p1_id,
        p2.id AS p2_id
    FROM people AS p1
        JOIN people AS p2
            ON p2.name = p1.name 
            AND
            (
                (
                    p2.birth_date IS NOT NULL 
                    AND p1.birth_date IS NOT NULL 
                    AND p2.birth_date = p1.birth_date
                )
                OR
                (
                    p2.email IS NOT NULL
                    AND p1.email IS NOT NULL 
                    AND p2.email = p1.email
                )
            )
            AND p2.id > p1.id 
)
SELECT
    d.p1_id AS id_tokeep,
    GROUP_CONCAT(d.p2_id ORDER BY d.p2_id SEPARATOR ',') AS ids_tomerge
FROM duplicates AS d
WHERE d.p1_id NOT IN (SELECT p2_id FROM duplicates)
GROUP BY d.p1_id
ORDER BY d.p1_id

whose key elements are:

• self-JOIN with the criteria of what 2 duplicates are, ie: same name AND (same birth date OR same email),
• the p2.id > p1.id ensure that a line does not match with itself and eliminate the symmetric duplicates (eg: line 2 is a duplicate of line 1, so is line 1 of line 2; then this condition eliminate the “line 1 is a duplicate of line 2”),
• the d.p1_id NOT IN (SELECT p2_id FROM duplicates) ensure the uniqueness of the line to keep;

gives exactly the result I looked for: the id of the line to keep and the ids of the lines to merge.

E.g. with these data:

| "id" | "name"     | "birth_date" | "email"                      |
|-----:|------------|--------------|------------------------------|
|    1 | "Gamow"    | "1904-03-04" | "[email protected]"    |
|    2 | "Gamow"    | "1904-03-04" | NULL                         |
|    3 | "Gamow"    | "1904-03-04" | "[email protected]"    |
|    4 | "Gamow"    | NULL         | "[email protected]"    |
|    5 | "Gamow"    | NULL         | NULL                         |
|    6 | "Gamow"    | "1904-03-05" | NULL                         |
|    7 | "Gamow"    | NULL         | "[email protected]"           |
|    8 | "Feynman"  | "1918-05-11" | "[email protected]" |
|    9 | "Feynman"  | "1918-05-11" | NULL                         |
|   10 | "Feynman"  | "1918-05-11" | NULL                         |
|   11 | "Poincaré" | "1854-04-29" | "[email protected]"  |
|   12 | "Poincaré" | "1854-04-29" | "[email protected]"  |
|   13 | "Poincaré" | "1986-04-04" | "[email protected]"    |
|   14 | "Poincaré" | "1986-04-04" | "[email protected]"    |
|   15 | "Poincaré" | NULL         | "[email protected]"    |

the result are:

| "id_tokeep" | "ids_tomerge" |
|------------:|---------------|
|           1 | "2,3,4"       |
|           8 | "9,10"        |
|          11 | "12"          |
|          13 | "14,15"       |

Try this on dbfiddle.uk.

NB: Using JSON_ARRAYAGG instead of GROUP_CONCAT would have been better (because the int type of the ids would have been conserved), but there is no (simple) way to sort the elements within a JSON_ARRAYAGG (cf this issue; as I wanted the ids to merge to be sorted, I have to use GROUP_CONCAT.