Finding rows with same values in multiple columns

Question

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

Answer 1

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 

Answer 2

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