Drill 7 of 10 in Database Analyst
0

Database Analyst · DELETE … USING · keep the lowest id

7. Delete duplicate registrations

Concept: DELETE … USING · keep the lowest idshow

To remove duplicates but keep one copy, delete every row that has a twin with the same key and a smaller id: DELETE FROM t a USING t b WHERE a.key = b.key AND a.id > b.id. The first row of each group has no smaller twin, so it survives. Match on the whole key, or you'll delete rows that only look alike.

Example (different data):

DELETE FROM reviews a
USING reviews b
WHERE a.product_id = b.product_id
  AND a.user_id = b.user_id
  AND a.id > b.id;

Your task

The event signup form let people double-click Register, and the badge printer needs one row per person per event.

Delete duplicate rows from event_registrations, where a duplicate is the same event_code and attendee_email, keeping the row with the lowest id in each group. Then return id, event_code and attendee_email of the remaining rows, sorted by id.

Starting Postgres in your browser…

Checking your access…