How do you find duplicate records based on two columns (SQL, SQL server, TSQL, and development)?

 To find duplicate records based on two columns in SQL (using T-SQL for SQL Server), you can use a combination of the GROUP BY and HAVING clauses. Here's how you can do it step by step:

Example Scenario

Let’s assume you have a table Employees with the columns FirstName and LastName. You want to find all rows where both the FirstName and LastName values are duplicated.

SQL Query to Find Duplicate Records Based on Two Columns

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

Explanation:

• SELECT FirstName, LastName, COUNT(*): This part selects the two columns (FirstName and LastName) and counts how many times each combination of these two columns appears in the table.

• GROUP BY FirstName, LastName: This groups the rows based on the combination of FirstName and LastName. So, rows with the same FirstName and LastName will be grouped together.

• HAVING COUNT(*) > 1: This filters the grouped results to only include those combinations where the count is greater than 1, i.e., only the duplicate combinations.

Alternative: To Get All Duplicate Records

If you want to retrieve all the rows that are considered duplicates (including the original ones), you can use a JOIN or a CTE (Common Table Expression) to get the full records:

WITH DuplicateRecords AS (
    SELECT FirstName, LastName
    FROM Employees
    GROUP BY FirstName, LastName
    HAVING COUNT(*) > 1
)
SELECT E.*
FROM Employees E
INNER JOIN DuplicateRecords D
    ON E.FirstName = D.FirstName
    AND E.LastName = D.LastName;

Explanation:

• The CTE DuplicateRecords finds all the combinations of FirstName and LastName that have duplicates (count > 1).
• The JOIN is used to select all the rows from the Employees table that match these duplicate combinations.
• The result will show every occurrence of the duplicate records, including the original ones.

Use Case Example: Finding Duplicates in a Table

SELECT EmployeeID, FirstName, LastName, COUNT(*)
FROM Employees
GROUP BY EmployeeID, FirstName, LastName
HAVING COUNT(*) > 1;

This query finds duplicates based on the combination of EmployeeID, FirstName, and LastName.

Conclusion:

• GROUP BY groups rows based on the specified columns.
• HAVING COUNT(*) > 1 filters out unique rows and shows only the duplicates.
• Use a JOIN or CTE to retrieve all the duplicate records from the table.

This approach works well in SQL Server (T-SQL) for identifying duplicate rows based on one or more columns.