Finding duplicate records

You can use GROUP BY to display a list of duplicate records by counting the consolidated records. For example, using the Employee table:

SELECT LastName, COUNT(*) FROM Employee GROUP BY LastName HAVING COUNT(*) > 1;

As only the name "Smith" is duplicated in LastName, the results table looks like this:

LastName

COUNT(*)

Smith

2

As you can group on several fields, you can construct more complex de-duplication queries. Using the EmployeeSales table as an example, the following query picks out the first and last occurrence of duplicated sales region / employee combinations. It also calculates a count of how many duplicates occur.

SELECT Employee, Region, COUNT(*) AS Total, FIRST(SalesID) AS [First Match], LAST(SalesID) AS [Last Match] FROM EmployeeSales GROUP BY Employee, Region HAVING COUNT(*) > 1;

This produces the following table:

Employee

Region

Total

First Match

Last Match

John

North

2

1

2

John

South

2

5

8

Richard

North

3

4

7

Susan

South

2

3

9

See also:

Groups and aggregate functions

Grouping on more than one level

Other types of SQL statements