1

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' | 'george.gamow@science.ua'    |
|  2 | 'Gamow'    | NULL         | NULL                         |
|  3 | 'Gamow'    | '1904-03-04' | NULL                         |
|  4 | 'Gamow'    | '1904-03-04' | 'george-gamow@science.ua'    |
|  5 | 'Gamow'    | NULL         | 'george.gamow@science.ua'    |
|  6 | 'Feynman'  | '1918-05-11' | 'richard.feynman@science.us' |
|  7 | 'Feynman'  | '1918-05-11' | NULL                         |
|  8 | 'Poincaré' | '1854-04-29' | 'henri.poincare@science.fr'  |

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: 'george.gamow@science.ua' »
  • 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', 'george.gamow@science.ua'),  -- 1
    ('Gamow', NULL, NULL),  -- 2
    ('Gamow', '1904-03-04', NULL),  -- 3
    ('Gamow', '1904-03-04', 'george-gamow@science.ua'),  -- 4
    ('Gamow', NULL, 'george.gamow@science.ua'),  -- 5
    ('Feynman', '1918-05-11', 'richard.feynman@science.us'),  -- 6
    ('Feynman', '1918-05-11', NULL),  -- 7
    ('Poincaré', '1854-04-29', 'henri.poincare@science.fr')  -- 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;
2
  • OR produces true/false value, where true is represented as 1 and false as '0. What do you _want_ OR` to mean inside GROUP BY?
    – Rick James
    Apr 2 at 17:17
  • Yep, exactly, this is what I say in my (own) accepted answer. Initialy, I wrongly assume that the OR would be understand within the GROUP BY context (i.e. semantically different from the basic logical OR), but it’s not. Hence it is not possible to do what I want with a simple GROUP BY and I find another way with mainly a self-JOIN with specific ON clause.
    – lavalade
    Apr 3 at 8:46

2 Answers 2

0

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    george.gamow@science.ua
 2  Gamow     null          null
 6  Feynman   1918-05-11    richard.feynman@science.us
 8  Poincaré  1854-04-29    henri.poincare@science.fr

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.

1
  • Thanks :-)! It would work, but I cannot use this solution because (i) I have to log the merged duplicates, and (ii) the real table is in production within an application, so there is no way I can replace it!
    – lavalade
    Mar 22 at 10:42
0

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" | "george.gamow@science.ua"    |
|    2 | "Gamow"    | "1904-03-04" | NULL                         |
|    3 | "Gamow"    | "1904-03-04" | "george-gamow@science.ua"    |
|    4 | "Gamow"    | NULL         | "george.gamow@science.ua"    |
|    5 | "Gamow"    | NULL         | NULL                         |
|    6 | "Gamow"    | "1904-03-05" | NULL                         |
|    7 | "Gamow"    | NULL         | "gamow@science.ua"           |
|    8 | "Feynman"  | "1918-05-11" | "richard.feynman@science.us" |
|    9 | "Feynman"  | "1918-05-11" | NULL                         |
|   10 | "Feynman"  | "1918-05-11" | NULL                         |
|   11 | "Poincaré" | "1854-04-29" | "henri.poincare@science.fr"  |
|   12 | "Poincaré" | "1854-04-29" | "henri.poincare@science.fr"  |
|   13 | "Poincaré" | "1986-04-04" | "henri.poincare@email.fr"    |
|   14 | "Poincaré" | "1986-04-04" | "henri.poincare@email.fr"    |
|   15 | "Poincaré" | NULL         | "henri.poincare@email.fr"    |

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.