Find duplicates in SQL column

Question

I've got this table named log:

ID User_ID Machine_Number Email 1 100 12345 [email protected] 2 100 12345 [email protected] 3 101 67890 [email protected] 4 102 12345 [email protected]

I need to find the User_IDs of users with the same Machine_Number. In this case, I need a query that returns 100 and 102.

I've tried:

SELECT user_id, COUNT(machine_number) FROM log GROUP BY machine_number HAVING COUNT(machine_number) > 1

but that gives the count of each occurrence of the machine_number, i.e. User_ID Count(machine_number)

100 2 101 1 102 1

Any suggestions?

Shaharyar · Accepted Answer · 2019-02-12 12:03:39Z

1

I guess you want to get both user_id against same machine. Try group_concate:

SELECT group_concat(DISTINCT user_id), machine_number FROM log GROUP BY machine_number HAVING COUNT(machine_number) > 1

edited Feb 12, 2019 at 12:03

answered Feb 11, 2019 at 16:53

Shaharyar

12.5k4 gold badges50 silver badges70 bronze badges

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

2 Comments

SilenceKit Over a year ago

This one gives user_ids of 100,100,102 I need it to just give 100,102 and skip the duplicate

Shaharyar Over a year ago

@SilenceKit you can simply add distinct in group_concate

Md. Mustafizur Rahman · Accepted Answer · 2019-02-11 17:48:28Z

below code is in MS SQL syntax, but I think it will work fine in MYSQL.

 SELECT DISTINCT t1.User_ID FROM log t1 INNER JOIN ( SELECT l.Machine_Number,count(DISTINCT l.User_ID)Count FROM log l GROUP BY l.Machine_Number )t2 ON t1.Machine_Number =t2.Machine_Number WHERE t2.Count>1

Zaynul Abadin Tuhin · Accepted Answer · 2019-02-11 16:54:02Z

use exists

select distinct t1.* from log t1 where exists ( select 1 from log t2 where t1.machine_number=t2.machine_number group by machine_number having count(*)>1 )

Gordon Linoff · Accepted Answer · 2019-02-11 18:22:34Z

I assume you mean different uses with the same machine. You can use exists like this to get the original rows:

select l.* from log l where exists (select 1 from log l2 where l2.machine_number = l.machine_number and l2.email <> l.email );

This should have very good performance, particularly with an index on (machine_number, email).

Mohammad Shahjada Sagor · Accepted Answer · 2019-02-11 19:47:54Z

Select DISTINCT M.User_Id, M.Machine_NUmber from Machine M Inner Join (Select M1.Machine_NUmber from Machine M1 Group By M1.Machine_NUmber Having COUNT(M1.Machine_NUmber)>1) M2 On M.Machine_NUmber = M2.Machine_NUmber

A blob of code is not a great answer. Convince us that this addresses the question posed with a short explanation.

Blaztix · Accepted Answer · 2019-02-11 20:35:56Z

SELECT user_id,Machine_Number FROM log where Machine_Number IN (SELECT Machine_Number FROM log GROUP BY Machine_Number HAVING count(1) > 1) ORDER BY Machine_Number;

And if you want distinct user_id then use below.

SELECT distinct user_id, Machine_Number FROM log where Machine_Number IN (SELECT Machine_Number FROM log GROUP BY Machine_Number HAVING count(1) > 1) ORDER BY Machine_Number;

Collectives™ on Stack Overflow

Find duplicates in SQL column

6 Answers 6

2 Comments

Comments

Comments

Comments

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

2 Comments

Comments

Comments

Comments

1 Comment

Comments

Related