CODEWITHSUNDEEP
sqlsql-server
gunr2171
gunr2171

Reputation: 17447

Finding rows with same values in multiple columns

I am trying to find rows that have duplicate values, but only based off of a select number of columns, not a single column or the entire row. For example, if my table looked like this:

ID     Address    State    Name
-------------------------------
0      7 Brown    NY       John
1      3 Red      WX       Jane
2      7 Brown    WX       Ted
3      7 Brown    NY       Fred

My question would be:

Find all ID's for rows where the row's Address and State field matched another row's Address and State field.

The answer to this query would be:

ID    Address    State    Name
------------------------------
0     7 Brown    NY       John
3     7 Brown    NY       Fred

Any Ideas?

Suggestions: How to select multiple columns values same rows from single table

Upvotes: 42

Views: 112395

Answers (2)

SQLCurious
SQLCurious

Reputation: 524

select *
from #table1
where Addr + St in (select Addr + St as FullAddr
             from #table1
             group by Addr + St
             having count(Addr+St) > 1)

Upvotes: 4

Lamak
Lamak

Reputation: 70638

Try the following:

SELECT A.*
FROM YourTable A
INNER JOIN (SELECT Address, State
            FROM YourTable
            GROUP BY Address, State
            HAVING COUNT(*) > 1) B
ON A.Address = B.Address AND A.State = B.State

Upvotes: 58

Related Questions