This guide is specifically for PostgreSQL syntax.

SQL Query to Find Duplicate Records

Identify duplicate rows in a table using GROUP BY and HAVING.

Quick Answer

SELECT column, COUNT(*) FROM table GROUP BY column HAVING COUNT(*) > 1;

Explanation

GROUP BY groups identical values, and HAVING COUNT(*) > 1 filters to show only groups with more than one occurrence, indicating duplicates.

Query Variants

Single Column

SELECT email, COUNT(*) as cnt
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Multi Column

SELECT first_name, last_name, COUNT(*) as cnt
FROM users
GROUP BY first_name, last_name
HAVING COUNT(*) > 1;

With Ids

SELECT email, GROUP_CONCAT(id) as duplicate_ids
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Pro Tips

• HAVING filters after grouping, WHERE filters before
• Include all columns that define uniqueness in GROUP BY
• Use ROW_NUMBER() for more complex duplicate handling

Related SQL Queries

Continue learning with more SQL query examples:

PostgreSQL-Specific Notes

This page covers PostgreSQL syntax. Other databases may have different syntax for similar operations.