Skip to content
Onsite
5 / 44

Delete duplicate rows keeping the lowest id

EasyP1Coding

Table person:

ColumnType
idint (primary key)
emailtext

Write a single DELETE statement that removes duplicate emails, keeping for each email only the row with the lowest id. Do not use SELECT. Rows whose email is unique stay as they are.

Schema

CREATE TABLE person (
  id int PRIMARY KEY,
  email text NOT NULL
);

Example 1

Input
person: (1, 'a@x.com'), (2, 'b@x.com'), (3, 'a@x.com')
Output
person after the delete: (1, 'a@x.com'), (2, 'b@x.com')

Constraints

  • PostgreSQL dialect.
  • The result is checked by reading the table ordered by id.
  • A table without a unique key can use the physical row id ctid; this one has a primary key.

Hints

PostgreSQL
Run checks the 2 visible tests, Submit all 6.