Intermediate · Lesson 16 of 17

Find duplicate rows

Spot repeated values with GROUP BY and HAVING.

Group, count, keep the repeats

Group by the column (or columns) that should be unique, count the rows in each group, and keep groups with more than one. If two columns together define a duplicate, group by both.

Seeing the full duplicate rows

The grouped result only shows the values. To see whole rows, join back to the original table or use COUNT(*) OVER (PARTITION BY ...) and filter on it.

Examples

Repeated titles

HAVING filters after the grouping, which WHERE cannot do.

SELECT title, COUNT(*) AS n FROM employees GROUP BY title HAVING COUNT(*) > 1 ORDER BY n DESC;

Full rows with a window count

Keeps every column, so you can inspect the duplicates.

SELECT * FROM (SELECT e.*, COUNT(*) OVER (PARTITION BY title) AS n FROM employees e) t WHERE n > 1;