LeetCode sql problems

Questions

  1. Swap Salary
  2. Duplicate Emails
  3. Delete Duplicate Emails

How To Find Duplicate Values in MySQL

Find duplicate rows

  1. Prepare sample table

    DROP TABLE IF EXISTS contacts;

CREATE TABLE contacts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(255) NOT NULL
);

INSERT INTO contacts (first_name,last_name,email) 
VALUES ('Carine ','Schmitt','carine.schmitt@verizon.net'),
       ('Jean','King','jean.king@me.com'),
       ('Peter','Ferguson','peter.ferguson@google.com'),
       ('Janine ','Labrune','janine.labrune@aol.com'),
       ('Jonas ','Bergulfsen','jonas.bergulfsen@mac.com'),
       ('Janine ','Labrune','janine.labrune@aol.com'),
       ('Susan','Nelson','susan.nelson@comcast.net'),
       ('Zbyszek ','Piestrzeniewicz','zbyszek.piestrzeniewicz@att.net'),
       ('Roland','Keitel','roland.keitel@yahoo.com'),
       ('Julie','Murphy','julie.murphy@yahoo.com'),
       ('Kwai','Lee','kwai.lee@google.com'),
       ('Jean','King','jean.king@me.com'),
       ('Susan','Nelson','susan.nelson@comcast.net'),
       ('Roland','Keitel','roland.keitel@yahoo.com');

  2. Query data

SELECT * FROM contacts ORDER BY email;
  1. Find duplicate values in one column
SELECT email, COUNT(email)
FROM
    contacts
GROUP BY email
HAVING COUNT(email) > 1;
  1. Find duplicate values in multiple columns
SELECT
    first_name, COUNT(first_name),
    last_name,  COUNT(last_name),
    email,      COUNT(email)
FROM
    contacts
GROUP BY
    first_name,
    last_name,
    email
HAVING
    COUNT(first_name) > 1
    AND COUNT(last_name) > 1
    AND COUNT(email) > 1;

How To Delete Duplicate Rows in MySQL

  1. Prepare sample data

    DROP TABLE IF EXISTS contacts;

CREATE TABLE contacts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(255) NOT NULL
);

INSERT INTO contacts (first_name,last_name,email) 
VALUES ('Carine ','Schmitt','carine.schmitt@verizon.net'),
       ('Jean','King','jean.king@me.com'),
       ('Peter','Ferguson','peter.ferguson@google.com'),
       ('Janine ','Labrune','janine.labrune@aol.com'),
       ('Jonas ','Bergulfsen','jonas.bergulfsen@mac.com'),
       ('Janine ','Labrune','janine.labrune@aol.com'),
       ('Susan','Nelson','susan.nelson@comcast.net'),
       ('Zbyszek ','Piestrzeniewicz','zbyszek.piestrzeniewicz@att.net'),
       ('Roland','Keitel','roland.keitel@yahoo.com'),
       ('Julie','Murphy','julie.murphy@yahoo.com'),
       ('Kwai','Lee','kwai.lee@google.com'),
       ('Jean','King','jean.king@me.com'),
       ('Susan','Nelson','susan.nelson@comcast.net'),
       ('Roland','Keitel','roland.keitel@yahoo.com');

  2. Duplicate emails in the table

SELECT
    email, COUNT(email)
FROM
    contacts
GROUP BY
    email
HAVING
    COUNT(email) > 1;
  1. Delete duplicate rows
  1. Delete duplicate rows using DELETE JOIN statement
-- Query first delete second

SELECT t1.* FROM contacts t1
INNER JOIN contacts t2
WHERE
    t1.id < t2.id AND
    t1.email = t2.email;

-- Delete duplicate rows and keeps the highest id

DELETE t1 FROM contacts t1
INNER JOIN contacts t2
WHERE
    t1.id < t2.id AND
    t1.email = t2.email;

-- Delete duplicate rows and keep the lowest id

DELETE c1 FROM contacts c1
INNER JOIN contacts c2
WHERE
    c1.id > c2.id AND
    c1.email = c2.email;
  1. Delete duplicate rows using an intermediate table
1. Create a new table with the structure the same as the original table that you want to delete duplicate rows.
2. Insert distinct rows from the original table to the immediate table.
3. Drop the original table and rename the immediate table to the original table.

-- step 1
CREATE TABLE contacts_temp
LIKE contacts;

-- step 2 Only work for disabled sql_mode=only_full_group_by
INSERT INTO contacts_temp
SELECT *
FROM contacts
GROUP BY email;

-- step 3
DROP TABLE contacts;

ALTER TABLE contacts_temp
RENAME TO contacts;
  1. Delete duplicate rows using the ROW_NUMBER() function

Note

ROW_NUMBER() function has been supported since MySQL version 8.02 so you should check your MySQL version before using the function.

  • The following statement uses the ROW_NUMBER() function to assign a sequential integer number to each row. If the email is duplicate, the row number will be greater than one.
SELECT
  id,
  email,
ROW_NUMBER() OVER (
  PARTITION BY email
  ORDER BY email
) AS row_num
FROM contacts;
  • The following statement returns id list of the duplicate rows:
SELECT
    id
FROM (
    SELECT
        id,
        ROW_NUMBER() OVER (PARTITION BY email ORDER BY email) AS row_num
    FROM
        contacts
) t
WHERE
    row_num > 1;
  • Delete the duplicate rows from the contacts table using the DELETE statement with a subquery in the WHERE clause:
DELETE FROM contacts
WHERE
    id IN (
    SELECT
        id
    FROM (
        SELECT
            id,
            ROW_NUMBER() OVER (
                PARTITION BY email
                ORDER BY email) AS row_num
        FROM
            contacts

    ) t
    WHERE row_num > 1
);