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

Perhaps tag with "TSQL" ?

JosephDoggie
– JosephDoggie

2019-04-23 15:36:25 +00:00
Commented Apr 23, 2019 at 15:36 — JosephDoggie
– JosephDoggie, Commented Apr 23, 2019 at 15:36

Lamak · Accepted Answer · 2012-06-15 18:30:52Z

59

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

answered Jun 15, 2012 at 18:30

Lamak

70.8k12 gold badges119 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

gunr2171 Over a year ago

ok, while these both work just fine. I think i will stick with this one. Thanks.

himanshupareek66 Over a year ago

Better and simple way to handle this kind of scenario stackoverflow.com/questions/13807314/…

Lamak Over a year ago

@himanshupareek66 I fail to see the difference. The reason for the join here we because op wanted the whole row where there were duplicates, not just address and state

SQLCurious · Accepted Answer · 2012-06-15 18:41:57Z

4

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

answered Jun 15, 2012 at 18:41

SQLCurious

5243 silver badges10 bronze badges

1 Comment

user330315 Over a year ago

The string concatenation operator in SQL is || not +. But it would be better to use where (addr, st) in (select addr, st from ...) anyway instead of concatenating strings (which might have problems with some combinations of address and state)

Collectives™ on Stack Overflow

Finding rows with same values in multiple columns

2 Answers 2

3 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Linked

Related