Retrieve duplicate values in a database table with SQL

There was an error with one of our applications, and I needed to find table rows with duplicate values. The table column was not defined to have a unique index, but we weren't anticipating duplicate values. I needed an easier way to identify duplicate values in a table with a simple SQL statement.

Consider the following "employees" table:

idNameAliasAge
1John DoeJohn30
2John SmithJohn40
3Joe SchmoJoe38
4Charlie BohneCharlie55

Assuming that we have an "employees" table with the above values, and looking to find records with duplicate "Alias". How do we retrieve them? With the following SQL statement with HAVING clause, we can easily accomplish that.

SELECT alias, count(alias) as count
FROM employee
GROUP BY alias
HAVING (count(alias) > 1)
ORDER BY alias

The above SQL statement will retrieve:

AliasCount
John2

*NOTE: The HAVING clause allows SQL to use aggregate functions with a condition whereas WHERE clause does not offer that functionality. For example, the HAVING clause can be used to retrieve SUM(x) > 100 or COUNT(y) > 1.

Share this post

Comments (0)

    No comment

Leave a comment

All comments are moderated. Spammy and bot submitted comments are deleted. Please submit the comments that are helpful to others, and we'll approve your comments. A comment that includes outbound link will only be approved if the content is relevant to the topic, and has some value to our readers.


Login To Post Comment