I have a table directors and need to get all duplicated rows

Checking columns is name and phone_number

Table directors

I need these rows:

ecause two upper rows has same phone number and last record has same name as first row

What I tried is

select d1.* from directors as d1
join (
   select d2.* from directors d2 
   group by `d2`.`uuid` 
   having count(d2.phone_number) > 1
   or count(d2.name) > 1
) d2 on d1.uuid = d2.uuid;

CodePudding user response：

Just one of possible options:

select t.* from t
join (
    select phone_number from t group by phone_number having count(phone_number) > 1
) d on t.phone_number = d.phone_number;

https://sqlize.online/sql/psql14/f7d63b0d5d06a4d6d428798da644dcbb/

One more example:

select t.* from t
join t t_copy using(phone_number)
where t.uuid != t_copy.uuid;

https://sqlize.online/s/MW

CodePudding user response：

A couple of options you can use:

select * 
from t
where exists (
    select * from t t2 
    where t2.phone_number = t.phone_number and t2.uuid != t.uuid
);

select * from (
    select *, Count(*) over(partition by phone_number) cnt
    from t
)t
where cnt > 1