How to find duplicate records

Question

Is there any way to filter to show duplicates in a specific column?

Answer 1

There are two basic ways to find duplicate records and show them using dbFront.

To find duplicates you start by creating a query that GROUP s the records BY the column(s) that you want to search on and then adds a HAVING clause that selects groups with a *COUNT() > 1**.

e.g. The following query finds all FirstNames in the Customer table that appear more then once.

SELECT dup.FirstName 
FROM [Customer] dup
GROUP BY dup.Firstname
HAVING count(*) > 1

Find Duplicates using a Custom Search

If you are searching for duplicates in a single column then you can easily accomplish this using a Custom Search button.

The search button allows you to specify a SQL WHERE clause so you can easily select all records that have a value that is contained in a sub query.

The button expression would be {[FirstName]} IN (%dup query%)

{[FirstName]} IN (
    SELECT dup.FirstName 
    FROM [Customer] dup
    GROUP BY dup.Firstname
    HAVING count(*) > 1
)

1. Create a Custom Search Button,
2. Name it "Find Duplicates",
3. Set the Search Expression to the SQL above,
4. Save the Button, and click on it to activate the filter.

This is a basic example that can be extended as needed. The benefit is that no extra SQL objects are required.

Important: If you add a custom search button then you likely want to create a second button that clears the custom search. This is needed as a separate step because some environments only allow users to switch between defined searches, and never view the entire table unfiltered.

Finding Duplicates using a Joined Table View

If you need to search on multiple columns or if you want extra detail then the best solution is to join on a VIEW that does the hard work of finding the duplicates.

The following SQL finds all customers that have the same FirstName, LastName and Phone number and it also returns the vDuplicateCount, and the vFirstCustomerID.

SELECT
    dup.FirstName, dup.LastName, dup.Phone,
    count(*) vDuplicateCount, 
    Min(dup.CustomerID) vFirstCustomerID
FROM [Customer] dup
GROUP BY dup.Firstname,dup.LastName, dup.Phone
HAVING count(*) > 1

You can create a view using the specified SQL and join it to your Customer table. dbFront will automatically create the join on the common columns [FirstName, LastName, Phone] and allow you to add the vDuplicateCount and vFirstCustomer columns to your Grid or the Advanced Search.

From there it is trival to find all of the records with duplicates.