Description
Write a SQL query to delete all duplicate emails, keeping only the one with the smallest id. Then SELECT remaining rows ordered by id ascending. **Output columns (in order):** id, email
Table:
person| Column | Type |
|---|---|
| id | INT |
| TEXT |
Examples
Input:
CREATE TABLE person (id INT, email TEXT); INSERT INTO person VALUES (1, 'john@test.com'), (2, 'jane@test.com'), (3, 'john@test.com');Output:
1|john@test.com
2|jane@test.comExplanation:
john@test.com appears in rows 1 and 3. After deleting duplicates (keeping smallest id), row 3 is removed, leaving rows 1 and 2.
Input:
CREATE TABLE person (id INT, email TEXT); INSERT INTO person VALUES (1, 'alice@company.com'), (2, 'bob@work.org'), (3, 'alice@company.com'), (4, 'carol@email.net'), (5, 'bob@work.org');Output:
1|alice@company.com
2|bob@work.org
4|carol@email.netExplanation:
Multiple duplicates exist - alice@company.com appears in rows 1 and 3 (keep row 1), bob@work.org appears in rows 2 and 5 (keep row 2), and carol@email.net has no duplicates so it remains
Input:
CREATE TABLE person (id INT, email TEXT); INSERT INTO person VALUES (10, 'user@domain.com'), (5, 'admin@site.org'), (15, 'user@domain.com'), (8, 'guest@portal.net'), (12, 'admin@site.org');Output:
5|admin@site.org
8|guest@portal.net
10|user@domain.comExplanation:
IDs are not sequential - user@domain.com appears in rows 10 and 15 (keep row 10 with smaller ID), admin@site.org appears in rows 5 and 12 (keep row 5 with smaller ID), guest@portal.net has no duplicates
Constraints
- •
Keep row with smallest id