﻿ SQL Challenges-1: Remove Duplicate Emails - w3resource

# SQL Challenges-1: Remove Duplicate Emails

## SQL Challenges-1: Exercise-7 with Solution

From the following table, write a SQL query to remove all the duplicate emails of employees keeping the unique email with the lowest employee id. Return employee id and unique emails.

Input:

Table: employees

Structure:

FieldTypeNullKeyDefaultExtra
employee_idint(11)YES
employee_namevarchar(255)YES
email_idvarchar(255)YES

Data:

employee_idemployee_nameemail_id
101Liam Alton[email protected]
102Josh Day[email protected]
103Sean Mann[email protected]
104Evan Blake[email protected]
105Toby Scott[email protected]

Sample Solution:

SQL Code(MySQL):

``````CREATE TABLE IF NOT EXISTS employees(employee_id int, employee_name varchar(255), email_id varchar(255));
TRUNCATE TABLE employees;
INSERT INTO employees (employee_id,employee_name, email_id) VALUES ('101','Liam Alton', '[email protected]');
INSERT INTO employees (employee_id,employee_name, email_id) VALUES ('102','Josh Day', '[email protected]');
INSERT INTO employees (employee_id,employee_name, email_id) VALUES ('103','Sean Mann', '[email protected]');
INSERT INTO employees (employee_id,employee_name, email_id) VALUES ('104','Evan Blake', '[email protected]');
INSERT INTO employees (employee_id,employee_name, email_id) VALUES ('105','Toby Scott', '[email protected]');
SELECT * FROM employees;

DELETE e1 FROM employees e1,  employees e2
WHERE
e1.email_id = e2.email_id AND e1.employee_id > e2.employee_id;
SELECT * FROM employees;
```
```

Sample Output:

```employee_id|employee_name|email_id     |
-----------|-------------|-------------|
101|Liam Alton   |[email protected]|
102|Josh Day     |[email protected]|
103|Sean Mann    |[email protected]|
104|Evan Blake   |[email protected]|
```

SQL Code Editor:

Contribute your code and comments through Disqus.

Previous: Customers without any Order.
Next: Rising Sulfur Dioxide.

﻿

## SQL: Tips of the Day

How to select the nth row in a SQL database table?

Basically, PostgreSQL and MySQL supports the non-standard:

```SELECT...
LIMIT y OFFSET x
```

Oracle, DB2 and MSSQL supports the standard windowing functions:

```SELECT * FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
columns
FROM tablename
) AS foo
WHERE rownumber <= n
```

Database: SQL

Ref: https://bit.ly/3zPxcD8