MySql selecting duplicated data (by group by and in)

MySql

Today I needed to check some duplicated data from our database which uses mysql.
We can ‘see’ the list of duplicated data by using group by phrase like this.

1
select * from (table name) group by (column name) having count(column name)>1;

But in this case, we can’t check the details of those data.
The situation I needed to solve was that I needed to check A column’s values of the rows having same B column with any other data.

So, I needed to check all of the rows having any duplicated value in B column.
After searching a bit, I made this query. The way to work is like this.

  1. searching for the list of the data having same B value in any row
  2. selecting all data of the rows having same value of the result from 1.
  • I used order by B, A for sorting them by same B 1st and then A 2nd
1
select * from tableName where B in (select B from tableName group by B having count(B)>1);

This one worked how I expected and I could add where phrase and order by phrase too.

Comments