Find the duplicates
Find values that repeat, and the rows that hold them, even when they differ in case. About 10 minutes.
Group, then keep the big groups
A duplicate is a value that shows up in more than one row. Group the rows by that value, count each group, and keep only the groups bigger than one. That is the whole pattern: GROUP BY, then HAVING COUNT(*) greater than 1.
For rows that repeat across several columns, group by all of them: same name and same city.
SELECT email, COUNT(*) AS times FROM signups GROUP BY email HAVING COUNT(*) > 1;
| id | |
|---|---|
| 1 | a@x.com |
| 2 | b@x.com |
| 3 | a@x.com |
| 4 | c@x.com |
| 5 | a@x.com |
GROUP BY email: a@x.com has three rows in its group.
Near-duplicates and the rows themselves
Real data is messy: A@x.com and a@x.com are the same person. Normalise before grouping with lower() and trim(), and group by that expression.
The query above gives you the repeated values. To list the actual rows, add a count over a partition, COUNT(*) OVER (PARTITION BY email), in a subquery, and keep rows where it is above 1. Or join back to the duplicated values.
SELECT id, email
FROM (
SELECT id, email,
COUNT(*) OVER (
PARTITION BY lower(email)
) AS n
FROM signups
)
WHERE n > 1;Which clause keeps only the emails used by more than one row?
- AGROUP BY email HAVING COUNT(*) > 1
- BWHERE COUNT(*) > 1
- CSELECT DISTINCT email
Show the answer
GROUP BY email HAVING COUNT(*) > 1. HAVING filters groups after they are counted.
Duplicate signups
The signups table has one row per signup. Some people signed up more than once with different capital letters. Show each email that appears more than once, ignoring capital letters, in lowercase as email, with how many times it appears as times. Order by times, highest first, then by email.
signups(id, email, name)
Up to 2,000 signups