Here’s a recipe for retrieving duplicate values in a SQL query (run on Postgres).
Consider a table called
id | name ----+---------------- 1 | Pop 2 | Rock 3 | Blues 4 | Pop
An enthusiastic listener appears to have added ‘Pop’ twice, and in this case there was no
UNIQUE constraint to prevent it.
To find duplicates in
genres, use a query like the following:
SELECT name, COUNT(*) FROM genres GROUP BY name HAVING COUNT(*) > 1;
name | count ------+------- Pop | 2
Note that an alias can’t be used for the count in this case, because the
HAVING clause is evaluated first.
This strategy can be used anywhere to find duplicates, or more generally to retrieve counts beyond any value, or some other criterion.