How to find and delete duplicate rows in SQL

1Find duplicate values with GROUP BY and HAVINGRecommended

GROUP BY title folds every row with the same title into one group, and COUNT(*) counts the rows in it. WHERE can't filter on an aggregate, because it runs before the grouping, so the filter goes in HAVING, which runs after it: HAVING COUNT(*) > 1 keeps only the groups with more than one row.

CREATE TABLE songs (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    artist TEXT NOT NULL
);
INSERT INTO songs (id, title, artist) VALUES
    (1, 'Help!', 'The Beatles'),
    (2, 'Hello', 'Adele'),
    (3, 'Help!', 'The Beatles'),
    (4, 'Hello', 'Oasis'),
    (5, 'Hello', 'Adele'),
    (6, 'Help!', 'The Beatles'),
    (7, 'Hurt', 'Johnny Cash');

-- Each title that appears more than once, and how often
SELECT title, COUNT(*) AS copies
FROM songs
GROUP BY title
HAVING COUNT(*) > 1
ORDER BY title;

Both Hello and Help! show 3 copies, and Hurt drops out because it appears once. But the Hello count is misleading: one of the three is the Oasis song, a different record that only shares the title. Grouping by one column is right when that column alone must be unique (a product code, a username); for this table a duplicate is the same title by the same artist, which is the next section. This query runs unchanged in PostgreSQL, MySQL and SQL Server.

2Duplicates across several columns

List every column that has to match in the GROUP BY, and put the same columns in the SELECT so you can see which combination repeats. Wrapping the query in a subquery and adding up copies - 1 tells you how many rows a cleanup will remove, since one row per group stays.

CREATE TABLE songs (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    artist TEXT NOT NULL
);
INSERT INTO songs (id, title, artist) VALUES
    (1, 'Help!', 'The Beatles'),
    (2, 'Hello', 'Adele'),
    (3, 'Help!', 'The Beatles'),
    (4, 'Hello', 'Oasis'),
    (5, 'Hello', 'Adele'),
    (6, 'Help!', 'The Beatles'),
    (7, 'Hurt', 'Johnny Cash');

-- A duplicate is the same title AND the same artist
SELECT title, artist, COUNT(*) AS copies
FROM songs
GROUP BY title, artist
HAVING COUNT(*) > 1
ORDER BY title;

-- How many rows a cleanup would remove
SELECT SUM(copies - 1) AS extra_rows
FROM (
    SELECT COUNT(*) AS copies
    FROM songs
    GROUP BY title, artist
    HAVING COUNT(*) > 1
);

Now Hello by Adele has 2 copies and Help! by The Beatles 3, while the Oasis row is on its own and no longer counts. extra_rows is 3: one surplus Adele row plus two surplus Beatles rows. Leave the id out of the GROUP BY: it is different on every row, so including it would put each row in a group of its own. For more on grouping by a column list, see GROUP BY multiple columns.

3List every duplicate row, not just the groups

GROUP BY collapses each group into one line, so it can't show you the ids you are about to delete. A window function counts the same groups without collapsing anything: COUNT(*) OVER (PARTITION BY title, artist) adds each row's group size as a column, and an outer query keeps the rows where it is above 1. The second query is the older form, for databases without window functions: join the table back to the duplicate groups.

CREATE TABLE songs (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    artist TEXT NOT NULL
);
INSERT INTO songs (id, title, artist) VALUES
    (1, 'Help!', 'The Beatles'),
    (2, 'Hello', 'Adele'),
    (3, 'Help!', 'The Beatles'),
    (4, 'Hello', 'Oasis'),
    (5, 'Hello', 'Adele'),
    (6, 'Help!', 'The Beatles'),
    (7, 'Hurt', 'Johnny Cash');

-- Window function: count each row's group without collapsing the rows
SELECT id, title, artist, copies
FROM (
    SELECT id, title, artist,
           COUNT(*) OVER (PARTITION BY title, artist) AS copies
    FROM songs
) AS counted
WHERE copies > 1
ORDER BY title, id;

-- Older form: join the rows back to the duplicate groups
SELECT s.id, s.title, s.artist
FROM songs AS s
JOIN (
    SELECT title, artist
    FROM songs
    GROUP BY title, artist
    HAVING COUNT(*) > 1
) AS d ON d.title = s.title AND d.artist = s.artist
ORDER BY s.title, s.id;

Both queries return the same five rows: ids 2 and 5 for Adele, and 1, 3 and 6 for The Beatles. The window version can't filter on copies in its own WHERE (window functions are computed after WHERE), which is why it sits in a subquery. The join version has a trap with NULL: d.artist = s.artist is never true when both are NULL, so a group of rows with no artist is found by the GROUP BY but then dropped by the join. The window version doesn't have that problem.

4Delete duplicates, keeping the lowest id

To delete all copies but one, pick a survivor in each group and delete everything else. The portable way: SELECT MIN(id) … GROUP BY title, artist returns one id per group, the oldest row if ids only go up, and DELETE … WHERE id NOT IN (…) removes every row that isn't on that list. Rows that were never duplicated are their own group, so their id is on the list too and they stay.

CREATE TABLE songs (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    artist TEXT NOT NULL
);
INSERT INTO songs (id, title, artist) VALUES
    (1, 'Help!', 'The Beatles'),
    (2, 'Hello', 'Adele'),
    (3, 'Help!', 'The Beatles'),
    (4, 'Hello', 'Oasis'),
    (5, 'Hello', 'Adele'),
    (6, 'Help!', 'The Beatles'),
    (7, 'Hurt', 'Johnny Cash');

-- Keep the lowest id in each (title, artist) group, delete the rest
DELETE FROM songs
WHERE id NOT IN (
    SELECT MIN(id)
    FROM songs
    GROUP BY title, artist
);

SELECT changes() AS deleted;

SELECT id, title, artist FROM songs ORDER BY id;

changes() (SQLite's affected-row count) reports 3 deleted rows, the same number section 2 predicted. One row per song is left: ids 1, 2, 4 and 7. Use MAX(id) instead to keep the newest insert. Before running a delete like this on real data, run the subquery or the section 3 listing first, or do it inside a transaction you can roll back. The same statement runs in PostgreSQL and SQL Server.

MySQL rejects it with error 1093, You can't specify target table 'songs' for update in FROM clause, because the subquery reads the table being deleted from. Wrapping the subquery in a derived table makes MySQL build the id list first, and then it works: WHERE id NOT IN (SELECT keep_id FROM (SELECT MIN(id) AS keep_id FROM songs GROUP BY title, artist) AS keepers). MySQL also has a self-join form, DELETE s1 FROM songs AS s1 JOIN songs AS s2 ON s2.title = s1.title AND s2.artist = s1.artist AND s2.id < s1.id, which deletes every row that has a lower-id twin; see delete using a join for that syntax in each database. Both MySQL forms deleted the same 3 rows on MySQL 8.4.

5Choose which copy to keep with ROW_NUMBER()

MIN(id) only keeps the first row inserted. When the survivor should be the newest, the most complete, or picked by any other rule, number the copies in each group with ROW_NUMBER() OVER (PARTITION BY … ORDER BY …). The ORDER BY inside the window decides which row gets number 1, and that is the one you keep: delete every row numbered 2 or more. Here the table has an added date, and the newest copy wins.

CREATE TABLE songs (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    artist TEXT NOT NULL,
    added TEXT NOT NULL
);
INSERT INTO songs (id, title, artist, added) VALUES
    (1, 'Help!', 'The Beatles', '2026-03-02'),
    (2, 'Hello', 'Adele', '2026-01-06'),
    (3, 'Help!', 'The Beatles', '2026-02-10'),
    (4, 'Hello', 'Oasis', '2026-02-11'),
    (5, 'Hello', 'Adele', '2026-03-01'),
    (6, 'Help!', 'The Beatles', '2026-01-05'),
    (7, 'Hurt', 'Johnny Cash', '2026-01-20');

-- Number the copies in each group, newest first; row 1 is the keeper
WITH ranked AS (
    SELECT id,
           ROW_NUMBER() OVER (
               PARTITION BY title, artist
               ORDER BY added DESC, id DESC
           ) AS rn
    FROM songs
)
DELETE FROM songs
WHERE id IN (SELECT id FROM ranked WHERE rn > 1);

SELECT id, title, artist, added FROM songs ORDER BY id;

This time Adele keeps id 5 (added 2026-03-01) rather than id 2, because the newest copy ranks first; for The Beatles the newest happens to be id 1. The id DESC after added DESC breaks ties, so two copies added the same day still get distinct numbers and exactly one survives. This exact statement ran unchanged on PostgreSQL, MySQL 8.4 and SQL Server (Babelfish), and MySQL raises no error 1093 for it. SQL Server also lets you delete straight from the CTE (DELETE FROM ranked WHERE rn > 1); that shortcut is SQL Server only, PostgreSQL rejects it, and Babelfish, the SQL Server stand-in used to test this page, doesn't support it, so it wasn't run here. The same ROW_NUMBER() pattern with rn = 1 is how you select the first row in each group.

6Which should you use?

7Prevent new duplicates with a UNIQUE index

A cleanup only fixes the past. Once each group is down to one row, a UNIQUE index on the same columns makes the database refuse any insert or update that would create a duplicate again. Create it after the cleanup: on a table that still has duplicates, creating the index fails. To skip a duplicate quietly instead of failing, SQLite and PostgreSQL accept ON CONFLICT … DO NOTHING.

CREATE TABLE songs (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    artist TEXT NOT NULL
);
INSERT INTO songs (id, title, artist) VALUES
    (1, 'Help!', 'The Beatles'),
    (2, 'Hello', 'Adele'),
    (4, 'Hello', 'Oasis');

-- After the cleanup, stop new duplicates at the source
CREATE UNIQUE INDEX songs_title_artist ON songs (title, artist);

-- Skip a row that would break the index (SQLite 3.24+, PostgreSQL)
INSERT INTO songs (title, artist) VALUES ('Hello', 'Adele')
ON CONFLICT (title, artist) DO NOTHING;

SELECT COUNT(*) AS songs FROM songs;

-- A plain INSERT of a duplicate now fails
INSERT INTO songs (title, artist) VALUES ('Help!', 'The Beatles');

The ON CONFLICT insert adds nothing, so the count stays at 3. The last insert fails with UNIQUE constraint failed: songs.title, songs.artist; PostgreSQL reports the same case as duplicate key value violates unique constraint "songs_title_artist". In your application, catch that error, or use an upsert when the new row should update the existing one instead; see insert or update if it exists.