AdvancedWindow Functions

Classic: find duplicate records and row to keep

The query

SQL

SELECT *
FROM (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY email, first_name, last_name
      ORDER BY emp_id ASC  -- keep lowest ID (oldest)
    ) AS rn
  FROM employees
) t
WHERE rn > 1; -- These are duplicates to delete

-- Delete duplicates
DELETE FROM employees
WHERE emp_id IN (
  SELECT emp_id FROM (
    SELECT emp_id,
      ROW_NUMBER() OVER (PARTITION BY email ORDER BY emp_id) AS rn
    FROM employees
  ) t
  WHERE rn > 1
);

Tested against PostgreSQL 16

Note

“ROW_NUMBER() partitioned by duplicate-defining columns. rn > 1 = duplicates. Classic dedup pattern.

Tables referenced

employees10 cols