How to Remove Duplicates in SQL Without a Unique ID

Why "no unique ID" breaks the usual DELETE

DELETE needs to target specific rows, and normally a WHERE id = 5 does that unambiguously. Without a primary key, two rows can be byte-for-byte identical across every column — which means any WHERE clause built from the table's own columns matches both duplicates at once, or neither. There's no column value that identifies "this one, not that one." The fix in every technique below is the same: manufacture an identity that doesn't come from the data itself.

Starting table:

Three completely identical Ana rows — the goal is to end up with exactly one.

The ROW_NUMBER + CTE pattern

Works on PostgreSQL, SQL Server, Snowflake, BigQuery, and Databricks, all of which support deleting through a CTE:

WITH ranked AS (
  SELECT
    ROW_NUMBER() OVER (
      PARTITION BY email, name   -- every column that defines "duplicate"
      ORDER BY (SELECT NULL)     -- no meaningful order; any tiebreak is fine
    ) AS rn
  FROM customers
)
DELETE FROM ranked WHERE rn > 1;

The PARTITION BY list must include every column that together defines what "duplicate" means — leaving one out means rows that differ only in the omitted column get lumped into the same group and one of them is deleted incorrectly. ORDER BY (SELECT NULL) is intentional: there's no meaningful order among identical rows, so any arbitrary tiebreak that keeps exactly one is fine.

Verify before you delete

Because there's no unique ID to double-check against afterward, run the identical logic as a SELECT first and inspect the rows that would be removed:

SELECT *,
  ROW_NUMBER() OVER (PARTITION BY email, name ORDER BY (SELECT NULL)) AS rn
FROM customers
ORDER BY email, name;

PostgreSQL: using ctid

Every PostgreSQL row has an internal physical location identifier, ctid, whether or not the table has a primary key. It's a convenient built-in stand-in for row identity:

DELETE FROM customers
WHERE ctid NOT IN (
  SELECT MIN(ctid)
  FROM customers
  GROUP BY email, name
);

ctid isn't stable across a VACUUM FULL or table rewrite, so it's only safe to reference within a single statement or transaction — never store it for later use.

Oracle: using ROWID

Oracle's equivalent is ROWID, a pseudo-column present on every row regardless of key structure:

DELETE FROM customers
WHERE ROWID NOT IN (
  SELECT MIN(ROWID)
  FROM customers
  GROUP BY email, name
);

The safe fallback: rebuild with DISTINCT

When a database makes row-identity tricks awkward, or the deduplication logic feels risky to run as an in-place DELETE, rebuilding the table from a deduplicated copy sidesteps the whole problem:

CREATE TABLE customers_deduped AS
SELECT DISTINCT * FROM customers;

DROP TABLE customers;
ALTER TABLE customers_deduped RENAME TO customers;

Simple and hard to get wrong, but it needs enough disk space for a full second copy of the table, momentarily loses any indexes or constraints (which need to be recreated on the new table), and isn't safe to run while other processes are actively writing to the original.

Partial duplicates: same key, different other columns

Sometimes "duplicate" doesn't mean every column matches — it means a specific subset does (like email), while other columns (like last_login) legitimately differ between the copies. The pattern is identical, just with a smaller PARTITION BY list and a deliberate ORDER BY to control which copy survives:

WITH ranked AS (
  SELECT
    ROW_NUMBER() OVER (
      PARTITION BY email
      ORDER BY last_login DESC   -- keep the most recently active copy
    ) AS rn
  FROM customers
)
DELETE FROM ranked WHERE rn > 1;

Unlike the true-duplicate case, this ORDER BY isn't arbitrary — it decides which of several genuinely different rows gets kept, so it should be chosen deliberately (most recent, highest ID, non-null over null, etc.).

Common mistakes

• Leaving a column out of PARTITION BY. Any column not included gets ignored entirely, silently merging rows that differ only in that column into the same "duplicate" group.
• Running the DELETE without previewing it first. With no unique key to sanity-check against afterward, there's no easy way to confirm the right rows were removed after the fact.
• Using ctid or ROWID outside a single transaction. Both can change after a table rewrite or vacuum — never persist them for later reuse.
• Forgetting to add a real primary key or unique constraint afterward. Deduplicating without preventing future duplicates just delays the same cleanup.
• Not accounting for NULLs in the PARTITION BY columns — most databases treat two NULLs as equal for grouping purposes here, which is usually, but not always, the desired behavior.