How to Find Duplicate Rows Across Multiple Columns in SQL?

Finding Duplicates Across Multiple Columns

In SQL, you can encounter situations where you need to identify rows with duplicate values across multiple columns. Suppose you have a table called "stuff" containing columns like id, name, and city. You want to search for rows with identical values in both the name and city columns.

SQL Query

To achieve this, you can utilize the following SQL query:

select s.id, t.* 
from [stuff] s
join (
    select name, city, count(*) as qty
    from [stuff]
    group by name, city
    having count(*) > 1
) t on s.name = t.name and s.city = t.city

Explanation

• The main query (select s.id, t.*) selects the id column from the original table ([stuff]) and all columns from a subquery (t).
• The subquery (select name, city, count(*) as qty) groups the table by name and city and counts the number of occurrences for each pair.
• The having count(*) > 1 clause filters the subquery to include only name and city pairs with counts greater than 1 (i.e., duplicates).
• The join condition (on s.name = t.name and s.city = t.city) links the original table to the subquery based on name and city, selecting rows where the combination appears multiple times.

Output

This query will return the following output, showing the id and name-city pairs that have duplicates:

id      name  city   
904834  jim   London  
904835  jim   London  
90145   Fred  Paris   
90132   Fred  Paris
90133   Fred  Paris

The above is the detailed content of How to Find Duplicate Rows Across Multiple Columns in SQL?. For more information, please follow other related articles on the PHP Chinese website!