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
– Dai, Commented Jun 22, 2021 at 0: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
– Dai, Commented Jun 22, 2021 at 0:18
Might want to check out stackoverflow.com/questions/5901791/… — M G
– M G, Commented Jun 22, 2021 at 0:19
@Strawberry, Ok, I want to close this question, please help me — Wang Liang
– Wang Liang, Commented Jun 22, 2021 at 8:19

Cetin Basoz · Accepted Answer · 2021-06-22 00:20:50Z

1

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)

answered Jun 22, 2021 at 0:20

Cetin Basoz

24k3 gold badges34 silver badges48 bronze badges

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

Comments

Gordon Linoff · Accepted Answer · 2021-06-22 00:22:43Z

1

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;

answered Jun 22, 2021 at 0:22

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

desap · Accepted Answer · 2021-06-22 20:41:11Z

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, 2021 at 20:41

answered Jun 22, 2021 at 0:28

desap

2051 silver badge16 bronze badges

Collectives™ on Stack Overflow

sql select duplicate rows with multiple OR condition

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related