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
2 Answers
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
select *
from #table1
where Addr + St in (select Addr + St as FullAddr
from #table1
group by Addr + St
having count(Addr+St) > 1)
