SQL
Delete Duplicate Rows Keeping One (General SQL)
Learn a general SQL method to efficiently remove duplicate records from a table, retaining only one instance of each duplicate set based on a specified ordering criterion.
DELETE FROM your_table
WHERE id IN (
SELECT id
FROM (
SELECT
id,
ROW_NUMBER() OVER (PARTITION BY column1, column2 ORDER BY created_at DESC) as rn
FROM
your_table
) AS subquery
WHERE rn > 1
);
How it works: This query removes duplicate rows from `your_table`, keeping only the newest record based on the `created_at` timestamp. It uses a subquery with `ROW_NUMBER()` to assign a rank within partitions defined by `column1` and `column2`. Rows with `rn > 1` are duplicates, and their `id`s are collected to be deleted from the main table, effectively keeping the record with the highest `created_at` value.