Drill 2 of 12 in General questions
0

Interview Question Bank · MIN(id) per group · IN subquery

2. Keep the first row per email

EasyGeneralQuestion #194

Concept: MIN(id) per group · IN subqueryshow

To deduplicate without deleting anything, decide which row survives in each group, then select only those rows. MIN(id) per email in a subquery names the survivors, and WHERE id IN (...) keeps their full rows. SELECT DISTINCT doesn't do this: it only merges rows that match in every selected column.

Example (different data):

SELECT *
FROM support_tickets
WHERE id IN (
  SELECT MIN(id) FROM support_tickets GROUP BY customer_id, subject
);

Your task

Larkspur Books is moving its newsletter list to a new mail tool, and each address must appear only once. The rule is to keep the row with the lowest id for each email.

Return id, email, full_name and signed_up_on for the lowest-id row of every email address. Sort by id.

Starting Postgres in your browser…

Checking your access…