E.g. lets say you have ‘table’ with column ‘slug’ and then there are 3 records with the same slug value and you want to remove 2nd, 3rd but keep the 1st.
select * FROM table a INNER JOIN table b ON a.slug = b.slug WHERE a.id > b.id;
You create an inner join on the same table – joining them by the column that the row duplicates are based on – in this case, column ‘slug’. What this select outputs are exactly the rows you wish to delete – 2nd and 3rd row.
Because in WHERE clause we specify that only those rows that have ID higher than the first occurrence.
Now to actually delete these records, you just put a delete in front, e.g.
delete from table where id IN (select id FROM table a INNER JOIN table b ON a.common_column = b.common_column WHERE a.id > b.id);