Find Duplicate Rows in SQL

Example query

This example assumes: customers(id bigint, email text, name text, created_at timestamptz). It is written in PostgreSQL syntax, and your table and column names will differ from these.

SELECT LOWER(TRIM(email)) AS email_key,
       COUNT(*) AS copies,
       MIN(id) AS oldest_id,
       ARRAY_AGG(id ORDER BY id) AS all_ids
FROM customers
WHERE email IS NOT NULL
GROUP BY LOWER(TRIM(email))
HAVING COUNT(*) > 1
ORDER BY copies DESC;

How it works

• LOWER(TRIM(email)) normalises case and stray spaces, so 'Ann@x.com ' and 'ann@x.com' count as the same address.
• GROUP BY that key and HAVING COUNT(*) > 1 keep only keys that appear more than once.
• ARRAY_AGG lists every id in each group, which tells you which rows to review or merge.

MySQL, BigQuery, SQL Server and SQLite differences

• ARRAY_AGG is PostgreSQL and BigQuery. In MySQL use GROUP_CONCAT(id), in SQL Server STRING_AGG(id, ','), and in SQLite group_concat(id).

Common mistakes

• Exact-match grouping misses near duplicates such as typos or 'Jon' and 'John'; fuzzy matching is a separate step.
• Two real people can share a work address or a phone number; review before merging.
• Deciding which duplicate is the 'real' one needs a rule (oldest, most complete, most recently updated).

Ask for it in plain English

Instead of writing the query yourself, you can type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. Check that the query matches the definition your business uses.

• Which customers appear more than once with the same email address?
• Show me duplicate product SKUs and how many copies there are.