Suppose you have one table where you need to show all of the rows that have duplicate (more than one) values in a particular column. For example you may have a column called UniqueNumber in your table. You want to know if any number in that column exists more than once. You can use a query to do that. You can optionally show any number of other columns in that query as well.

Have a look at our History table. Some of the data may have changed form other blog posts using this data. Are there any duplicates in the UniqueNumber column? It’s easy to spot those when the file is small like this. Both Frank and Bob have the same UniqueNumber (third column) as 23456. Following the History table listing is the query that you can use to find duplicates in a single column in a single table.

John      12        12987     
Frank     121       23456     
Sally     647       11211     
Bob       24        23456     
Sally     7876      22222     

SELECT History.[FirstName], History.[Amount], History.[UniqueNumber]
FROM History
WHERE (((History.[UniqueNumber]) In (SELECT [UniqueNumber] FROM [History] As Tmp GROUP BY [UniqueNumber] HAVING Count(*)>1 )))
ORDER BY History.[UniqueNumber];