Every so often it seems I need to delete duplicated rows in a table. I usually first turn to Google to find a solution only to waste 10-15 minutes finding the right approach that doesn't overcomplicate the process by generating temp tables and such.

Anyway, I wanted to make a note of the solution I just used because it's simple and will more often than not solve my problem.

First, to return the duplicated rows:

SELECT column_a, column_b, count(*)
FROM table_name
HAVING count(*) > 1
GROUP BY column_a, column_b;

Then to delete the duplicates:

DELETE FROM table_name
WHERE rowid NOT IN
(SELECT max(rowid) FROM table_name
     GROUP BY column_a, column_b
     HAVING count(*)>=1);