sql select duplicate rows with multiple OR condition

Question

I checked https://stackoverflow.com/a/8149330/11343720 but, I want change the condition with OR instead of AND.
I need get dupliate data with duplicate name or duplicate city.
How I can do this?

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 OR s.city = t.city

So I want to do something like this SQL code below:

select s.id, s.name,s.city 
from stuff s
group by s.name having count(where city OR name are identical) > 1

@MERN Why is that "your requirement"? Who is giving you your "requirements"? (Sounds to me like you have a micromanager...) Are you perhaps confusing the English-language definition of "or" and "and" as opposed to the formal-logic definition of "or" and "and" which are actually very different to the English-language definitions? — Dai, Jun 22 '21 at 00:15
We need you to post **concrete examples** of the "duplicate data" you expect to see - because an overly-inclusive join criteria **will always** result in duplicate rows in the output, but that doesn't mean there's duplicate *source data*. — Dai, Jun 22 '21 at 00:18
Might want to check out https://stackoverflow.com/questions/5901791/is-having-an-or-in-an-inner-join-condition-a-bad-idea — M G, Jun 22 '21 at 00:19
@Strawberry, Ok, I want to close this question, please help me — Wang Liang, Jun 22 '21 at 08:19
Now that you've got answers, closing (by which I assume you mean 'deleting') seems rude to me. I'd suggest either accepting one of the answers or improving the question. — Strawberry, Jun 22 '21 at 08:28

score 1 · Answer 1 · answered Jun 22 '21 at 00:20

Maybe I misunderstood you initially. You want either name or city duplications, right?

select s.* 
from [stuff] s
where name in 
(
    select name
    from [stuff]
    group by name
    having count(*) > 1
) OR
city in (select city
    from [stuff]
    group by city
    having count(*) > 1)

score 1 · Answer 2 · answered Jun 22 '21 at 00:22

I need get duplicate data with duplicate name or duplicate city.

I would suggest window functions:

select s.*
from (select s.*,
             count(*) over (partition by name) as name_cnt,
             count(*) over (partition by city) as city_cnt
      from stuff s
     ) s
where name_cnt > 1 or city_cnt > 1;

desap · Answer 3 · 2021-06-22T20:41:11.200

0

I tried to keep this solution similar to the original solution you linked.

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

edited Jun 22 '21 at 20:41

answered Jun 22 '21 at 00:28

desap

169
1
15

sql select duplicate rows with multiple OR condition

3 Answers3