SQL: How to find duplicates? | 3 Simple Ways

SELECT email, COUNT() FROM customers
GROUP BY email HAVING COUNT() > 1;

SQL: How to find duplicates?

In SQL Server, there are 3 main ways to find duplicates:

1.Use GROUP BY

To find duplicates using the GROUP BY method in SQL:

1. Select the columns that you want to check for duplicates.
2. Use the GROUP BY clause to group the data by those columns.
3. Use the HAVING clause to filter the results to show only the groups that have more than one entry.
4. Full example:
   sql

   SELECT email, COUNT() FROM customers
   GROUP BY email HAVING COUNT() > 1;

Example

Let's say you have a table called "customers" with columns "name" and "email":

To find duplicate email addresses, you could use the following SQL query:

SELECT email, COUNT() FROM customers GROUP BY email HAVING COUNT() > 1;

This query will group the customers by email address and count the number of entries in each group. It will then filter the results and show only the groups with more than one entry, which are duplicate email addresses.

Here's the result:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY name DESC) AS rn
    FROM customers
)
SELECT * FROM cte
WHERE rn > 1;

SELECT email,
    CASE
        WHEN email = LAG(email) OVER (ORDER BY email) THEN 'Yes'
        ELSE 'No'
    END AS duplicate_value
FROM customers
ORDER BY email;