Question 21560 in Mysql Question posted in
The official MySQL documentation can be found here.

how to get duplicate data from table with sql – Mysql

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

Checking columns is name and phone_number

Table directors

uuid name phone_number
5esd ari 111-222-333
6dcv lee 111-222-333
56js poo 667-784-343
tug8 ari 866-653-343

I need these rows:

uuid name phone_number
5esd ari 111-222-333
6dcv lee 111-222-333
tug8 ari 866-653-343

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;
Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search