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…